SELECT COUNT(*) 贵吗? [英] is SELECT COUNT(*) expensive?

问题描述

您认为在每次加载页面时计算一个非常大的表(例如 50K 行)中的条目是否是个好主意?

SELECT COUNT(*) FROM table

现在我有大约 2000 行，而且看起来非常快，我没有看到页面加载有任何延迟 :)

但该表最多应达到 50K 个条目......我很好奇它会如何加载

(ps:这个显示行数的页面是私有的，在管理界面中，不是公开的)

解决方案

COUNT(*) 被优化为如果 SELECT 从一个表中检索，没有检索到其他列，并且没有 WHERE 子句，则返回非常快.例如:

mysql>从学生中选择 COUNT(*);

<块引用>

此优化仅适用于 MyISAM 表，因为此存储引擎存储了准确的行数，并且可以非常快速地访问.

来源

正如您所说，您使用 MyISAM 并且您的查询是针对整个表的，它是 1 行还是 100000 行都没有关系.

Do you think it's a good idea to count entries from a really big table (like 50K rows) on each page load?

SELECT COUNT(*) FROM table

Right now I have like 2000 rows and seems pretty fast, I don't see any delays in page load :)

But the table should reach up to 50K entries... And I'm curious how it will load then

(ps: this page which shows the row count is private, in a Admin interface, not public)

解决方案

COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;

This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly.

Source

As you said you use MyISAM and your query is for the whole table, it doesn't matter if its 1 or 100000 rows.

这篇关于SELECT COUNT(*) 贵吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！