blocks|key|781165|text|在与我的队友交谈后，Ricardo告诉我们，更快的方法是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|781166|show+table+status+like+'<TABLE+NAME>'+\G|code-block|syntax|javascript|781167|但你必须记住，结果可能并不准确。|781168|您也可以从命令行使用它：|781169|$+mysqlshow+--status+<DATABASE>+<TABLE+NAME>|781170|更多信息：http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html|offset|length|781171|你可以在mysqlperformanceblog上找到完整的讨论|781172|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html|1|http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/^0|0|0|0|0|0|5|1P|0|0|4|K|1|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|15|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|17|8|@]|9|@$O|18|P|19|1|1A]]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@$O|1C|P|1D|1|1E]]|A|$]]|$1|S|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]]]

After speaking with my team-mates, Ricardo told us that the faster way is:

<pre><code>show table status like '&lt;TABLE NAME&gt;' \G
</code></pre>

But you have to remember that the result may not be exact.

You can use it from command line too:

<pre><code>$ mysqlshow --status &lt;DATABASE&gt; &lt;TABLE NAME&gt;
</code></pre>

More information: <a href="http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html">http://dev.mysql.com/doc/refman/5.7/en/show-table-status.html</a>

And you can find a complete discussion at <a href="http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/">mysqlperformanceblog</a>

blocks|key|995317|text|问得好，回答得好。这里有一个快速的方法来回应结果，如果任何人正在阅读这个页面，并错过了这一部分：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|995318|$counter+=+mysql_query("SELECT+COUNT(*)+AS+id+FROM+table");
$num+=+mysql_fetch_array($counter);
$count+=+$num["id"];
echo("$count");|code-block|syntax|javascript|995319|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|$]]

Great question, great answers. Here's a quick way to echo the results if anyone is reading this page and missing that part:

<pre><code>$counter = mysql_query("SELECT COUNT(*) AS id FROM table");
$num = mysql_fetch_array($counter);
$count = $num["id"];
echo("$count");
</code></pre>

blocks|key|995531|text|这个查询(类似于to+what+bayuah+posted)显示了一个数据库中所有表计数的一个很好的汇总：(我强烈推荐的stored+procedure+by+Ivan+Cachicatari的简化版本)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|995532|SELECT+TABLE_NAME+AS+'Table+Name',+TABLE_ROWS+AS+'Rows'+FROM+information_schema.TABLES+WHERE+TABLES.TABLE_SCHEMA+=+'`YOURDBNAME`'+AND+TABLES.TABLE_TYPE+=+'BASE+TABLE';+|code-block|syntax|javascript|995533|示例：|995534|%2B-----------------%2B---------%2B
%7C+Table+Name++++++%7C+Rows++++%7C
%2B-----------------%2B---------%2B
%7C+some_table++++++%7C+++10278+%7C
%7C+other_table+++++%7C+++++995+%7C|995535|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/31342781/117259|1|http://en.latindevelopers.com/ivancp/2012/a-better-show-table-status/^0|8|L|0|1O|10|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@$A|X|B|Y|1|Z]|$A|10|B|11|1|12]]|C|$]]|$1|D|3|E|5|F|7|13|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|14|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|15|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|16|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]|U|$5|P|Q|R|C|$S|V]]]]

This query (which is similar <a href="https://stackoverflow.com/a/31342781/117259">to what bayuah posted</a>) shows a nice summary of all tables count inside a database: 
(simplified version of <a href="http://en.latindevelopers.com/ivancp/2012/a-better-show-table-status/" rel="noreferrer">stored procedure by Ivan Cachicatari</a> which I highly recommend).

<pre><code>SELECT TABLE_NAME AS 'Table Name', TABLE_ROWS AS 'Rows' FROM information_schema.TABLES WHERE TABLES.TABLE_SCHEMA = '`YOURDBNAME`' AND TABLES.TABLE_TYPE = 'BASE TABLE'; 
</code></pre>

Example: 

<pre><code>+-----------------+---------+
| Table Name | Rows |
+-----------------+---------+
| some_table | 10278 |
| other_table | 995 |
</code></pre>

blocks|key|780895|text|我一直认为下面的方法会给我最快的响应时间。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|780896|SELECT+COUNT(1)+FROM+...+WHERE+...|code-block|syntax|javascript|780897|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|$]]

I've always understood that the below will give me the fastest response times.

<pre><code>SELECT COUNT(1) FROM ... WHERE ...
</code></pre>

blocks|key|995412|text|如果您需要获取整个结果集的计数，您可以采用以下方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|995413|SELECT+SQL_CALC_FOUND_ROWS+*+FROM+table_name+LIMIT+5;
SELECT+FOUND_ROWS();|code-block|syntax|javascript|995414|这通常不会比使用COUNT更快，尽管人们可能会认为情况正好相反，因为它在内部进行计算，而不是将数据发送回用户，因此性能改进是值得怀疑的。|offset|length|style|CODE|995415|执行这两个查询对于分页以获取总计很好，但对于使用WHERE子句则不是特别好。|995416|entityMap^0|0|0|8|5|0|O|5|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@$I|W|J|X|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

If you need to get the count of the entire result set you can take following approach:

<pre><code>SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 5;
SELECT FOUND_ROWS();
</code></pre>

This isn't normally faster than using <code>COUNT</code> albeit one might think the opposite is the case because it's doing the calculation internally and doesn't send the data back to the user thus the performance improvement is suspected.

Doing these two queries is good for pagination for getting totals but not particularly for using <code>WHERE</code> clauses.

blocks|key|995481|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|995482|SELECT
++++table_rows+"Rows+Count"
FROM
++++information_schema.tables
WHERE
++++table_name="Table_Name"
AND
++++table_schema="Database_Name";|code-block|syntax|javascript|995483|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|$]]

Try this:

<pre><code>SELECT
 table_rows "Rows Count"
FROM
 information_schema.tables
WHERE
 table_name="Table_Name"
AND
 table_schema="Database_Name";
</code></pre>

blocks|key|995813|text|这是能够获得最快结果的最佳查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|995814|SELECT+SQL_CALC_FOUND_ROWS+1+FROM+`orders`;
SELECT+FOUND_ROWS();|code-block|syntax|javascript|995815|在我的基准测试中:+0.448s|995816|​|995817|📷|atomic|offset|length|995818|995819|此查询耗时4.835秒|995820|SELECT+SQL_CALC_FOUND_ROWS+*+FROM+`orders`;
SELECT+FOUND_ROWS();|995821|995822|995823|995824|计数*耗时25.675秒|995825|SELECT+count(*)+FROM+`orders`;|995826|995827|995828|995829|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/06c0bee7d187e5efb05bca252b001eab.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/35488e638a980b81314ac3c03dd52b31.png|2|https://ask.qcloudimg.com/http-save/yehe-900000/2ab4b5bb979063ed15e8d2d3c25c9097.png^0|0|0|0|0|0|1|0|0|0|0|0|0|0|1|1|0|0|0|0|0|0|1|2|0|0^^$0|@$1|2|3|4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1I|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1J|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1K|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|1L|8|@]|9|@$N|1M|O|1N|1|1O]]|A|$]]|$1|P|3|J|5|6|7|1P|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|1R|8|@]|9|@]|A|$E|F]]|$1|U|3|J|5|6|7|1S|8|@]|9|@]|A|$]]|$1|V|3|L|5|M|7|1T|8|@]|9|@$N|1U|O|1V|1|1W]]|A|$]]|$1|W|3|J|5|6|7|1X|8|@]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1Y|8|@]|9|@]|A|$]]|$1|Z|3|10|5|D|7|1Z|8|@]|9|@]|A|$E|F]]|$1|11|3|J|5|6|7|20|8|@]|9|@]|A|$]]|$1|12|3|L|5|M|7|21|8|@]|9|@$N|22|O|23|1|24]]|A|$]]|$1|13|3|J|5|6|7|25|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|26|8|@]|9|@]|A|$]]]|15|$16|$5|17|18|19|A|$1A|1B|1C|-4]]|1D|$5|17|18|19|A|$1A|1E|1C|-4]]|1F|$5|17|18|19|A|$1A|1G|1C|-4]]]]

This is the best query able to get the fastest results.
<pre><code>SELECT SQL_CALC_FOUND_ROWS 1 FROM `orders`;
SELECT FOUND_ROWS();
</code></pre>
In my benchmark test: 0.448s
<a href="https://i.stack.imgur.com/osvFo.png" rel="noreferrer"><img src="https://i.stack.imgur.com/osvFo.png" alt="enter image description here" /></a>
This query takes 4.835s
<pre><code>SELECT SQL_CALC_FOUND_ROWS * FROM `orders`;
SELECT FOUND_ROWS();
</code></pre>
<a href="https://i.stack.imgur.com/1lvLD.png" rel="noreferrer"><img src="https://i.stack.imgur.com/1lvLD.png" alt="enter image description here" /></a>
count * takes 25.675s
<pre><code>SELECT count(*) FROM `orders`;
</code></pre>
<a href="https://i.stack.imgur.com/4Y3wY.png" rel="noreferrer"><img src="https://i.stack.imgur.com/4Y3wY.png" alt="enter image description here" /></a>

blocks|key|995361|text|也许你可能想要考虑做一个SELECT+max(Id)+-+min(Id)+%2B+1。只有当您的if是连续的并且行没有被删除时，这才会起作用。然而，它是非常快的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|995362|entityMap^0|C|S|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

Perhaps you may want to consider doing a <code>SELECT max(Id) - min(Id) + 1</code>. This will only work if your Ids are sequential and rows are not deleted. It is however very fast.

blocks|key|995625|text|EXPLAIN+SELECT+id+FROM+....为我做了这件事。我可以看到结果的rows列下的行数。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|995626|entityMap^0|0|R|17|4|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]|$9|K|A|L|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|M|8|@]|D|@]|E|$]]]|G|$]]

<code>EXPLAIN SELECT id FROM ....</code> did the trick for me. and I could see the number of rows under <code>rows</code> column of the result.

blocks|key|781586|text|如果您不需要超级精确的计数，那么您可以为当前会话设置较低的事务隔离级别。如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|781587|SET+TRANSACTION+ISOLATION+LEVEL+READ+UNCOMMITTED;
SELECT+count(*)+FROM+the_table+WHERE+...;
COMMIT;+/*+close+the+transaction+*/|code-block|syntax|javascript|781588|拥有一个与WHERE条件匹配的索引也是很好的。|offset|length|style|CODE|781589|它确实加快了大型InnoDB表的计算速度。我在一个有大约700M行和重载的表上进行了检查，它工作正常。它将查询时间从~451秒减少到~2秒。|781590|我从下面的答案中得到了这个想法：https://stackoverflow.com/a/918092/1743367|781591|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/918092/1743367^0|0|0|5|5|0|0|G|16|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@$I|11|J|12|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|14|8|@]|9|@$I|15|J|16|1|17]]|A|$]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

If you don't need super-exact count, then you can set a lower transaction isolation level for the current session. Do it like this:
<pre><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT count(*) FROM the_table WHERE ...;
COMMIT; /* close the transaction */
</code></pre>
It is also good to have an index that matches the <code>WHERE</code> condition.
It really speeds up the counting for big InnoDB tables. I checked it on a table with ~700M rows and heavy load, it works. It reduced the query time from ~451 seconds to ~2 seconds.
I took the idea from this answer: <a href="https://stackoverflow.com/a/918092/1743367">https://stackoverflow.com/a/918092/1743367</a>

blocks|key|995568|text|我为德国政府处理有时有6000万条记录的表格。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|995569|我们需要知道很多倍的总行数。|995570|因此，我们数据库程序员决定，在每个表中都有一条记录，其中存储了总记录数。我们根据INSERT或DELETE行更新了这个数字。|995571|我们尝试了所有其他方法。这是目前为止最快的方法。|995572|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|$]]

I handled tables for the German Government with sometimes 60 million records. 

And we needed to know many times the total rows.

So we database programmers decided that in every table is record one always the record in which the total record numbers is stored. We updated this number, depending on INSERT or DELETE rows.

We tried all other ways. This is by far the fastest way.

blocks|key|781415|text|在主键上带有where条件的count(*)语句为我返回行数的速度要快得多，从而避免了全表扫描。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|781416|SELECT+COUNT(*)+FROM+...+WHERE+<PRIMARY_KEY>+IS+NOT+NULL;|code-block|syntax|javascript|781417|对我来说这比|781418|SELECT+COUNT(*)+FROM+...|781419|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

A count(*) statement with a where condition on the primary key returned the row count much faster for me avoiding full table scan.

<pre><code>SELECT COUNT(*) FROM ... WHERE &lt;PRIMARY_KEY&gt; IS NOT NULL;
</code></pre>

This was much faster for me than

<pre><code>SELECT COUNT(*) FROM ...
</code></pre>

Which way to count a number of rows should be faster in MySQL?

This:

<pre><code>SELECT COUNT(*) FROM ... WHERE ...
</code></pre>

Or, the alternative:

<pre><code>SELECT 1 FROM ... WHERE ...

// and then count the results with a built-in function, e.g. in PHP mysql_num_rows()
</code></pre>

One would think that the first method should be faster, as this is clearly database territory and the database engine should be faster than anybody else when determining things like this internally.

MySQL: Fastest way to count number of rows

数据库

MySQL

在MySQL中，哪种计算行数的方法应该更快？这一点：SELECT COUNT(*) FROM ... WHERE ...或者，另一种选择：SELECT 1 FROM ... WHERE ...// and then count the results with a built-in function, e.g. in PHP mysql_num_rows()有人会认为第一种方法应该更快，因为这显然

问MySQL:计算行数的最快方法
EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:计算行数的最快方法EN

回答 12

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySQL:计算行数的最快方法
EN