This is a simple question about efficiency specifically related to the MySQL implementation. I want to just check if a table is empty (and if it is empty, populate it with the default data) . Would it be best to use a statement like SELECT COUNT(*) FROM 'table' and then compare to 0, or would it be better to do a statement like SELECT 'id' FROM 'table' LIMIT 0,1 then check if any results were returned (the result set has next)?
这是一个关于效率与MySQL实现特别相关的简单问题。我想检查一个表是否为空(如果它是空的,则使用默认数据填充它)。是否最好使用SELECT COUNT(*)FROM'table'之类的语句,然后比较为0,或者最好做一个像SELECT'id'FROM'table'LIMIT 0,1之类的语句然后检查是否有返回结果(结果集有下一个)?
Although I need this for a project I am working on, I am also interested in how MySQL works with those two statements and whether the reason people seem to suggest using COUNT(*) is because the result is cached or whether it actually goes through every row and adds to a count as it would intuitively seem to me.
虽然我需要这个用于我正在开发的项目,但我也对MySQL如何处理这两个语句感兴趣,以及人们似乎建议使用COUNT(*)的原因是因为结果是缓存还是它实际上是通过每个行,并添加到计数,因为它在我看来是直观的。
2 个解决方案
#1
4
It is better to do the second method or just exists. Specifically, something like:
最好是做第二种方法或只是存在。具体来说,类似于:
if exists (select id from table)
should be the fastest way to do what you want. You don't need the limit; the SQL engine takes care of that for you.
应该是做你想做的最快的方式。你不需要限制; SQL引擎为您处理这个问题。
By the way, never put identifiers (table and column names) in single quotes.
顺便说一下,永远不要将标识符(表名和列名)放在单引号中。
#2
0
You should definitely go with the second query rather than the first.
你绝对应该使用第二个查询而不是第一个查询。
When using COUNT(*), MySQL is scanning at least an index and counting the records. Even if you would wrap the call in a LEAST() (SELECT LEAST(COUNT(*), 1) FROM table;) or an IF(), MySQL will fully evaluate COUNT() before evaluating further. I don't believe MySQL caches the COUNT(*) result when InnoDB is being used.
当使用COUNT(*)时,MySQL正在扫描至少一个索引并计算记录。即使你将调用包装在LEAST()(SELECT LEAST(COUNT(*),1)FROM表;)或IF()中,MySQL也会在进一步评估之前完全评估COUNT()。我不相信MySQL在使用InnoDB时会缓存COUNT(*)结果。
Your second query results in only one row being read, furthermore an index is used (assuming id is part of one). Look at the documentation of your driver to find out how to check whether any rows have been returned. By the way, the id field may be omitted from the query (MySQL will use an arbitrary index):
您的第二个查询导致只读取一行,此外还使用索引(假设id是一个的一部分)。查看驱动程序的文档,了解如何检查是否已返回任何行。顺便说一句,id字段可以从查询中省略(MySQL将使用任意索引):
SELECT 1 FROM table LIMIT 1;
However, I think the simplest and most performant solution is the following (as indicated in Gordon's answer):
但是,我认为最简单,最高效的解决方案如下(如Gordon的回答所示):
SELECT EXISTS (SELECT 1 FROM table);
EXISTS returns 1 if the subquery returns any rows, otherwise 0. Because of this semantic MySQL can optimize the execution properly. Any fields listed in the subquery are ignored, thus 1 or * is commonly written.
如果子查询返回任何行,则EXISTS返回1,否则返回0.由于此语义,MySQL可以正确地优化执行。子查询中列出的任何字段都将被忽略,因此通常会写入1或*。
See the MySQL Manual for more info on the EXISTS keyword and its use.
有关EXISTS关键字及其用法的更多信息,请参阅MySQL手册。
#1
4
It is better to do the second method or just exists. Specifically, something like:
最好是做第二种方法或只是存在。具体来说,类似于:
if exists (select id from table)
should be the fastest way to do what you want. You don't need the limit; the SQL engine takes care of that for you.
应该是做你想做的最快的方式。你不需要限制; SQL引擎为您处理这个问题。
By the way, never put identifiers (table and column names) in single quotes.
顺便说一下,永远不要将标识符(表名和列名)放在单引号中。
#2
0
You should definitely go with the second query rather than the first.
你绝对应该使用第二个查询而不是第一个查询。
When using COUNT(*), MySQL is scanning at least an index and counting the records. Even if you would wrap the call in a LEAST() (SELECT LEAST(COUNT(*), 1) FROM table;) or an IF(), MySQL will fully evaluate COUNT() before evaluating further. I don't believe MySQL caches the COUNT(*) result when InnoDB is being used.
当使用COUNT(*)时,MySQL正在扫描至少一个索引并计算记录。即使你将调用包装在LEAST()(SELECT LEAST(COUNT(*),1)FROM表;)或IF()中,MySQL也会在进一步评估之前完全评估COUNT()。我不相信MySQL在使用InnoDB时会缓存COUNT(*)结果。
Your second query results in only one row being read, furthermore an index is used (assuming id is part of one). Look at the documentation of your driver to find out how to check whether any rows have been returned. By the way, the id field may be omitted from the query (MySQL will use an arbitrary index):
您的第二个查询导致只读取一行,此外还使用索引(假设id是一个的一部分)。查看驱动程序的文档,了解如何检查是否已返回任何行。顺便说一句,id字段可以从查询中省略(MySQL将使用任意索引):
SELECT 1 FROM table LIMIT 1;
However, I think the simplest and most performant solution is the following (as indicated in Gordon's answer):
但是,我认为最简单,最高效的解决方案如下(如Gordon的回答所示):
SELECT EXISTS (SELECT 1 FROM table);
EXISTS returns 1 if the subquery returns any rows, otherwise 0. Because of this semantic MySQL can optimize the execution properly. Any fields listed in the subquery are ignored, thus 1 or * is commonly written.
如果子查询返回任何行,则EXISTS返回1,否则返回0.由于此语义,MySQL可以正确地优化执行。子查询中列出的任何字段都将被忽略,因此通常会写入1或*。
See the MySQL Manual for more info on the EXISTS keyword and its use.
有关EXISTS关键字及其用法的更多信息,请参阅MySQL手册。