blocks|key|350447|text|如果有问题的列不是NULL，那么您的两个查询是等价的。当group_id包含空值时，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350448|select+count(*)|code-block|syntax|javascript|350449|将对所有行进行计数，而|350450|select+count(group_id)|350451|将只计算group_id不为null的行数。|350452|此外，一些数据库系统，如MySQL，在您请求计数(*)时采用了优化，这使得这样的查询比特定的查询快一点。|350453|就我个人而言，当只是计数时，为了安全起见，我会使用count(*)来计算空值。|350454|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

If the column in question is NOT NULL, both of your queries are equivalent. When group_id contains null values, 

<pre><code>select count(*)
</code></pre>

will count all rows, whereas

<pre><code>select count(group_id)
</code></pre>

will only count the rows where group_id is not null.

Also, some database systems, like MySQL employ an optimization when you ask for count(*) which makes such queries a bit faster than the specific one.

Personally, when just counting, I'm doing count(*) to be on the safe side with the nulls.

blocks|key|355559|text|如果我没记错的话，在MYSQL中，COUNT(*)计算所有行，而COUNT(column_name)只计算给定列中具有非NULL值的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|355560|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If I remember it right, in MYSQL COUNT(*) counts all rows, whereas COUNT(column_name) counts only the rows that have a non-NULL value in the given column.

blocks|key|3133458|text|COUNT(*)计算所有行，而COUNT(column_name)将只计算指定列中没有NULL值的行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3133459|在MySQL中需要注意的重要事项：|3133460|对于*或非null列的MyISAM表，COUNT()非常快，因为行计数是缓存的。InnoDB没有行计数缓存，因此无论列是否为null，COUNT(*)或COUNT(column_name)的性能都没有差别。您可以在MySQL+performance博客上了解更多关于this+post的差异。|offset|length|3133461|entityMap|0|LINK|mutability|MUTABLE|url|http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/^0|0|0|3P|9|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

COUNT(*) count all rows while COUNT(column_name) will count only rows without NULL values in the specified column.

Important to note in MySQL:

COUNT() is very fast on MyISAM tables for * or not-null columns, since the row count is cached. InnoDB has no row count caching, so there is no difference in performance for COUNT(*) or COUNT(column_name), regardless if the column can be null or not. You can read more on the differences on <a href="http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/" rel="noreferrer">this post</a> at the MySQL performance blog.

blocks|key|3133442|text|如果您尝试使用SELECT+COUNT(1)+FROMgroup_relations，它会更快一些，因为它不会尝试从您的列中检索信息。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3133443|编辑:我刚刚做了一些研究，发现这种情况只发生在一些数据库中。在sqlserver中，使用1或*是相同的，但在oracle上使用1会更快。|3133444|http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/9367c580-087a-4fc1-bf88-91a51a4ee018/|3133445|显然，它们在mysql中没有区别，就像sqlserver一样，解析器似乎会将查询更改为select(1)。如果我在某些方面误导了你，很抱歉。|3133446|entityMap|0|LINK|mutability|MUTABLE|url^0|7|K|0|0|0|2U|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|W|8|@]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|J|3|K|5|6|7|10|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|11|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|I]]]]

if you try <code>SELECT COUNT(1) FROM</code>group_relations it will be a bit faster because it will not try to retrieve information from your columns.

Edit: I just did some research and found out that this only happens in some db. In sqlserver it's the same to use 1 or *, but on oracle it's faster to use 1.

<a href="http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/9367c580-087a-4fc1-bf88-91a51a4ee018/" rel="nofollow noreferrer">http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/9367c580-087a-4fc1-bf88-91a51a4ee018/</a>

Apparently there is no difference between them in mysql, like sqlserver the parser appears to change the query to select(1). Sorry if I mislead you in some way.

blocks|key|3133581|text|我自己也对此很好奇。阅读文档和理论答案都很好，但我喜欢将它们与经验证据进行平衡。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3133582|我有一个包含5,607,997条记录的MySQL表(InnoDB)。这个表在我自己的私有沙箱中，所以我知道它的内容是静态的，没有其他人在使用服务器。我认为这有效地消除了所有对性能的外部影响。我有一个包含auto_increment主键字段(+Id+)的表，我知道它永远不会为null，我将在where子句测试中使用它(WHERE+Id不是NULL)。|3133583|我在运行测试时看到的唯一另一个可能的故障是缓存。第一次运行查询总是比使用相同索引的后续查询慢。我将在下面将其称为缓存种子调用。为了稍微混淆一下，我用where子句运行了它，我知道无论任何数据(TRUE+=+TRUE)，它的计算结果都将始终为true。|3133584|这就是我的结果：|3133585|QueryType|3133586|++++++%7C++w/o+WHERE++++++++++%7C+where+id+is+not+null+%7C++where+true=true|code-block|syntax|javascript|3133587|COUNT()|3133588|++++++%7C++9+min+30.13+sec+%2B%2B+%7C+6+min+16.68+sec+%2B%2B+++%7C+2+min+21.80+sec+%2B%2B
++++++%7C++6+min+13.34+sec++++%7C+1+min+36.02+sec++++++%7C+2+min+0.11+sec+
++++++%7C++6+min+10.06+se+++++%7C+1+min+33.47+sec++++++%7C+1+min+50.54+sec|3133589|计数(Id)|3133590|++++++%7C++5+min+59.87+sec++++%7C+1+min+34.47+sec++++++%7C+2+min+3.96+sec+
++++++%7C++5+min+44.95+sec++++%7C+1+min+13.09+sec++++++%7C+2+min+6.48+sec|3133591|计数(1)|3133592|++++++%7C+6+min+49.64+sec++++%7C+2+min+0.80+sec+++++++%7C+2+min+11.64+sec
++++++%7C+6+min+31.64+sec++++%7C+1+min+41.19+sec++++++%7C+1+min+43.51+sec|3133593|%2B%2BThis被认为是缓存种子设定调用。预计它会比其他的慢一些。|3133594|我想说，结果不言而喻。COUNT(Id)通常比其他值更有优势。添加Where子句可以极大地减少访问时间，即使您知道它的计算结果为true。最佳点似乎是COUNT(Id)...其中Id不为空。|3133595|我很乐意看到其他人的结果，可能是使用较小的表，或者使用where子句针对不同的字段，而不是您正在计算的字段。我相信还有其他一些我没有考虑到的变化。|3133596|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1A|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1B|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1C|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|1D|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|1E|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|1F|8|@]|9|@]|A|$M|N]]|$1|S|3|T|5|6|7|1G|8|@]|9|@]|A|$]]|$1|U|3|V|5|L|7|1H|8|@]|9|@]|A|$M|N]]|$1|W|3|X|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Y|3|Z|5|L|7|1J|8|@]|9|@]|A|$M|N]]|$1|10|3|11|5|6|7|1K|8|@]|9|@]|A|$]]|$1|12|3|13|5|6|7|1L|8|@]|9|@]|A|$]]|$1|14|3|15|5|6|7|1M|8|@]|9|@]|A|$]]|$1|16|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]]|17|$]]

I was curious about this myself. It's all fine to read documentation and theoretical answers, but I like to balance those with empirical evidence.

I have a MySQL table (InnoDB) that has 5,607,997 records in it. The table is in my own private sandbox, so I know the contents are static and nobody else is using the server. I think this effectively removes all outside affects on performance. I have a table with an auto_increment Primary Key field (Id) that I know will never be null that I will use for my where clause test (WHERE Id IS NOT NULL).

The only other possible glitch I see in running tests is the cache. The first time a query is run will always be slower than subsequent queries that use the same indexes. I'll refer to that below as the cache Seeding call. Just to mix it up a little I ran it with a where clause I know will always evaluate to true regardless of any data (TRUE = TRUE).

That said here are my results:

QueryType 

<pre><code> | w/o WHERE | where id is not null | where true=true
</code></pre>

COUNT()

<pre><code> | 9 min 30.13 sec ++ | 6 min 16.68 sec ++ | 2 min 21.80 sec ++
 | 6 min 13.34 sec | 1 min 36.02 sec | 2 min 0.11 sec 
 | 6 min 10.06 se | 1 min 33.47 sec | 1 min 50.54 sec
</code></pre>

COUNT(Id)

<pre><code> | 5 min 59.87 sec | 1 min 34.47 sec | 2 min 3.96 sec 
 | 5 min 44.95 sec | 1 min 13.09 sec | 2 min 6.48 sec
</code></pre>

COUNT(1)

<pre><code> | 6 min 49.64 sec | 2 min 0.80 sec | 2 min 11.64 sec
 | 6 min 31.64 sec | 1 min 41.19 sec | 1 min 43.51 sec
</code></pre>

++This is considered the cache Seeding call. It is expected to be slower than the rest.

I'd say the results speak for themselves. COUNT(Id) usually edges out the others. Adding a Where clause dramatically decreases the access time even if it's a clause you know will evaluate to true. The sweet spot appears to be COUNT(Id)... WHERE Id IS NOT NULL.

I would love to see other peoples' results, perhaps with smaller tables or with where clauses against different fields than the field you're counting. I'm sure there are other variations I haven't taken into account.

blocks|key|350582|text|寻求替代方案|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350583|正如您所看到的，当表变得很大时，COUNT查询会变慢。我认为最重要的事情是考虑你试图解决的问题的性质。例如，许多开发人员在为大型记录集生成分页时使用COUNT查询，以便确定结果集中的总页数。|offset|length|style|CODE|350584|知道COUNT查询将变慢，您可以考虑另一种显示分页控件的方法，使您可以避开较慢的查询。谷歌的分页就是一个很好的例子。|350585|去规格化|350586|如果您一定要知道与特定计数匹配的记录数，请考虑使用经典的数据反规范化技术。考虑在插入记录时递增计数器，在删除记录时递减计数器，而不是在查找时计算行数。|350587|如果您决定这样做，请考虑使用幂等事务性操作来保持这些非正规化值的同步。|350588|BEGIN+TRANSACTION;
INSERT+INTO++`group_relations`+(`group_id`)+VALUES+(1);
UPDATE+`group_relations_count`+SET+`count`+=+`count`+%2B+1;
COMMIT;|code-block|syntax|javascript|350589|或者，如果您的RDBMS支持数据库触发器，您也可以使用它们。|350590|根据您的体系结构，使用memcached之类的缓存层来存储、递增和递减非正规化值，并在缺少缓存键时直接执行慢计数查询可能是有意义的。如果您有非常不稳定的数据，这可以减少总体写争用，尽管在这种情况下，您将需要考虑solutions+to+the+dog-pile+effect。|350591|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.kovyrin.net/2008/03/10/dog-pile-effect-and-how-to-avoid-it-with-ruby-on-rails-memcache-client-patch/^0|0|G|5|22|5|0|2|5|0|0|0|0|0|0|2X|W|0|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@$D|18|E|19|F|G]|$D|1A|E|1B|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|1C|8|@$D|1D|E|1E|F|G]]|9|@]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1G|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1H|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|1I|8|@]|9|@]|A|$S|T]]|$1|U|3|V|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|X|5|6|7|1K|8|@]|9|@$D|1L|E|1M|1|1N]]|A|$]]|$1|Y|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|Z|$10|$5|11|12|13|A|$14|15]]]]

<h2>Seek Alternatives</h2>

As you've seen, when tables grow large, <code>COUNT</code> queries get slow. I think the most important thing is to consider the nature of the problem you're trying to solve. For example, many developers use <code>COUNT</code> queries when generating pagination for large sets of records in order to determine the total number of pages in the result set.

Knowing that <code>COUNT</code> queries will grow slow, you could consider an alternative way to display pagination controls that simply allows you to side-step the slow query. Google's pagination is an excellent example.

<h2>Denormalize</h2>

If you absolutely must know the number of records matching a specific count, consider the classic technique of data denormalization. Instead of counting the number of rows at lookup time, consider incrementing a counter on record insertion, and decrementing that counter on record deletion.

If you decide to do this, consider using idempotent, transactional operations to keep those denormalized values in synch.

<pre><code>BEGIN TRANSACTION;
INSERT INTO `group_relations` (`group_id`) VALUES (1);
UPDATE `group_relations_count` SET `count` = `count` + 1;
COMMIT;
</code></pre>

Alternatively, you could use database triggers if your RDBMS supports them.

Depending on your architecture, it might make sense to use a caching layer like memcached to store, increment and decrement the denormalized value, and simply fall through to the slow COUNT query when the cache key is missing. This can reduce overall write-contention if you have very volatile data, though in cases like this, you'll want to consider <a href="http://blog.kovyrin.net/2008/03/10/dog-pile-effect-and-how-to-avoid-it-with-ruby-on-rails-memcache-client-patch/" rel="nofollow noreferrer">solutions to the dog-pile effect</a>.

blocks|key|350504|text|MySQL+ISAM表应优化计数(*)，跳过全表扫描。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350505|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

MySQL ISAM tables should have optimisation for COUNT(*), skipping full table scan.

blocks|key|3133550|text|计数中的星号与选择表中所有字段的星号无关。说COUNT(*)比COUNT(field)慢纯粹是胡说八道|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3133551|凭直觉，select+COUNT(*)比select+COUNT(field)快。如果RDBMS检测到您在count上指定了"*“而不是field，那么它不需要计算任何东西来递增COUNT。然而，如果您指定field+on+COUNT，RDBMS将始终计算您的字段是否为null，以对其进行计数。|3133552|但如果您的字段可以为空，请在COUNT中指定该字段。|3133553|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

An asterisk in COUNT has no bearing with asterisk for selecting all fields of table. It's pure rubbish to say that COUNT(*) is slower than COUNT(field)

I intuit that select COUNT(*) is faster than select COUNT(field). If the RDBMS detected that you specify "*" on COUNT instead of field, it doesn't need to evaluate anything to increment count. Whereas if you specify field on COUNT, the RDBMS will always evaluate if your field is null or not to count it.

But if your field is nullable, specify the field in COUNT.

blocks|key|3133562|text|COUNT(*)事实和迷思：|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3133563|MYTH："InnoDB不能很好地处理计数(*)查询“：|3133564|如果您有WHERE子句，则所有存储引擎都以相同的方式执行大多数count(*)查询，否则您的InnoDB将不得不执行全表扫描。|3133565|事实：如果没有where子句，InnoDB不会优化count(*)查询|3133566|entityMap^0|0|E|0|0|4|0|0|0|2|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|T|8|@]|D|@]|E|$]]|$1|J|3|K|5|6|7|U|8|@$9|V|A|W|B|C]]|D|@]|E|$]]|$1|L|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|M|$]]

COUNT(*) facts and myths:

MYTH: "InnoDB doesn't handle count(*) queries well": 

Most count(*) queries are executed same way by all storage engines if you have a WHERE clause, otherwise you InnoDB will have to perform a full table scan.

FACT: InnoDB doesn't optimize count(*) queries without the where clause

blocks|key|3346624|text|最好是按索引列计数，如主键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3346625|SELECT+COUNT(`group_id`)+FROM+`group_relations`|code-block|syntax|javascript|3346626|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It is best to count by an indexed column such as a primary key.

<pre><code>SELECT COUNT(`group_id`) FROM `group_relations`
</code></pre>

blocks|key|350461|text|正如Sebastian已经说过的那样，这应该取决于你实际想要实现的目标，也就是说，明确你的意图！如果你只是计算行数，那么就计算计数(*)，或者计算一列，就得到计数(+column+)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350462|这可能也值得去看看你的DB供应商。回到我使用Informix的时候，它对COUNT(*)进行了优化，与计算单个或多个列相比，它的查询计划执行成本为1，这将导致更高的数字|350463|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

It should depend on what you are actually trying to achieve as Sebastian has already said, i.e. make your intentions clear! If you are just counting the rows then go for the COUNT(*), or counting a single column go for the COUNT(column).

It might be worth checking out your DB vendor too. Back when I used to use Informix it had an optimisation for COUNT(*) which had a query plan execution cost of 1 compared to counting single or mutliple columns which would result in a higher figure

blocks|key|350507|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350508|如果您尝试从group_relations中选择COUNT(1)，速度会更快一些，因为它不会尝试从您的列中检索信息。|blockquote|350509|350510|350511|COUNT(1)过去比COUNT(*)快，但现在不是这样了，因为现代DBMS足够智能，知道您不想知道列|350512|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|-4|4|5|6|J|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|K|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|L|7|@]|8|@]|9|$]]|$1|E|3|-4|4|5|6|M|7|@]|8|@]|9|$]]|$1|F|3|G|4|5|6|N|7|@]|8|@]|9|$]]|$1|H|3|-4|4|5|6|O|7|@]|8|@]|9|$]]]|I|$]]

<blockquote>
 if you try SELECT COUNT(1) FROM group_relations it will be a bit faster because it will not try to retrieve information from your columns.
</blockquote>

COUNT(1) used to be faster than COUNT(*), but that's not true anymore, since modern DBMS are smart enough to know that you don't wanna know about columns

blocks|key|351893|text|我从MySQL那里得到的关于这类事情的建议是，一般来说，试图基于这样的技巧来优化查询，从长远来看可能是一种诅咒。在MySQL的历史上，有一些例子表明，依赖于优化器工作方式的高性能技术最终会成为下一个版本的瓶颈。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|351894|编写回答您提出的问题的查询--如果您想要计算所有行的数量，请使用COUNT(*)。如果您想要非空列的计数，可以使用COUNT(+col+)+WHERE+col不是NULL。适当地建立索引，并将优化工作留给优化器。尝试进行自己的查询级优化有时会降低内置优化器的效率。|351895|也就是说，您可以在查询中做一些事情来使优化器更容易地加快查询速度，但我不相信COUNT是其中之一。|351896|编辑:上面答案中的统计数据很有趣。我不确定在这种情况下优化器中是否真的有什么东西在起作用。我只是在谈论一般的查询级优化。|351897|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

The advice I got from MySQL about things like this is that, in general, trying to optimize a query based on tricks like this can be a curse in the long run. There are examples over MySQL's history where somebody's high-performance technique that relies on how the optimizer works ends up being the bottleneck in the next release. 

Write the query that answers the question you're asking -- if you want a count of all rows, use COUNT(*). If you want a count of non-null columns, use COUNT(col) WHERE col IS NOT NULL. Index appropriately, and leave the optimization to the optimizer. Trying to make your own query-level optimizations can sometimes make the built-in optimizer less effective.

That said, there are things you can do in a query to make it easier for the optimizer to speed it up, but I don't believe COUNT is one of them.

Edit: The statistics in the answer above are interesting, though. I'm not sure whether there is actually something at work in the optimizer in this case. I'm just talking about query-level optimizations in general.

blocks|key|350550|text|type|unstyled|depth|inlineStyleRanges|entityRanges|data|350551|我知道像这样做查询通常不是一个好主意：|blockquote|350552|350553|SELECT+*+FROM+group\_relations|offset|length|style|CODE|350554|但是当我只想要计数时，我是否应该执行这个查询，因为这允许表更改，但仍然会产生相同的结果。|350555|从group\_relations中选择计数(*)|350556|350557|正如您的问题所暗示的，SELECT+*不明智的原因是，对表的更改可能需要对代码进行更改。这不适用于COUNT(*)。很少有人想要SELECT+COUNT('group_id')为您提供的特定行为--通常您想知道记录数。这就是COUNT(*)的用途，所以使用它吧。|350558|entityMap^0|0|0|0|E|G|0|0|1|G|0|0|B|8|1D|8|1S|O|34|8|0^^$0|@$1|2|3|-4|4|5|6|T|7|@]|8|@]|9|$]]|$1|A|3|B|4|C|6|U|7|@]|8|@]|9|$]]|$1|D|3|-4|4|5|6|V|7|@]|8|@]|9|$]]|$1|E|3|F|4|5|6|W|7|@$G|X|H|Y|I|J]]|8|@]|9|$]]|$1|K|3|L|4|5|6|Z|7|@]|8|@]|9|$]]|$1|M|3|N|4|5|6|10|7|@$G|11|H|12|I|J]]|8|@]|9|$]]|$1|O|3|-4|4|5|6|13|7|@]|8|@]|9|$]]|$1|P|3|Q|4|5|6|14|7|@$G|15|H|16|I|J]|$G|17|H|18|I|J]|$G|19|H|1A|I|J]|$G|1B|H|1C|I|J]]|8|@]|9|$]]|$1|R|3|-4|4|5|6|1D|7|@]|8|@]|9|$]]]|S|$]]

<blockquote>
 I know it's generally a bad idea to do
 queries like this:

<pre><code>SELECT * FROM `group_relations`
</code></pre>
 
 But when I just want the count, should
 I go for this query since that allows
 the table to change but still yields
 the same results.

<pre><code>SELECT COUNT(*) FROM `group_relations`
</code></pre>
</blockquote>

As your question implies, the reason <code>SELECT *</code> is ill-advised is that changes to the table could require changes in your code. That doesn't apply to <code>COUNT(*)</code>. It's pretty rare to want the specialized behavior that <code>SELECT COUNT('group_id')</code> gives you - typically you want to know the number of records. That's what <code>COUNT(*)</code> is for, so use it.

I know it's generally a bad idea to do queries like this:

<pre><code>SELECT * FROM `group_relations`
</code></pre>

But when I just want the count, should I go for this query since that allows the table to change but still yields the same results.

<pre><code>SELECT COUNT(*) FROM `group_relations`
</code></pre>

Or the more specfic

<pre><code>SELECT COUNT(`group_id`) FROM `group_relations`
</code></pre>

I have a feeling the latter could potentially be faster, but are there any other things to consider?

Update: I am using InnoDB in this case, sorry for not being more specific.

Should I COUNT(*) or not?

我知道像这样做查询通常不是一个好主意：SELECT * FROM `group_relations`但是当我只想要计数时，我是否应该执行这个查询，因为这允许表更改，但仍然会产生相同的结果。SELECT COUNT(*) FROM `group_relations`或者更具体的SELECT COUNT(`group_id`) FROM `group_relations`我有一种感觉，后者可能会更快，

问我应该算(*)还是不算？
EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我应该算(*)还是不算？EN

回答 14

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问我应该算(*)还是不算？
EN