mysql 计数 vs mysql SELECT，哪个更快?

2021-12-30 00:00:00 select count mysql

如果我想检查一个名字，我想看看在用户表下的用户名"列中存在多少行/名字.让我们说数千...数十万，我应该使用:

If I want to do a check a name, I want to see how many rows/name exists in the "username" column under users table. Lets say thousands ... hundred of thousands, should I use:

  • 计数(名称)，

  • count(name),

count(*) 或

count(*) or

SELECT username FROM users where username = 'name'

SELECT username FROM users where username = 'name'

哪个更合适?或者他们会在速度/响应方面给出相同的结果?

Which one is the more appropriate? Or they will give same result in term of speed/response?

谢谢大家，我找到了答案，count() 肯定会更快

Thanks guys, I found the answer, count() will definitely faster

这个查询是否正确

SELECT COUNT( username )
FROM users
WHERE `username` = 'tim'

推荐答案

COUNT(*) 和 COUNT(Name) 可能会产生不同的值.COUNT 将不包括 NULL 值，因此如果 Name 的任何实例等于 NULL，它们将不会被计算在内.

COUNT(*) and COUNT(Name) might produce different values. COUNT will not include NULL values, so if there are any instances of Name that equal NULL they will not be counted.

COUNT(*) 也会比 Count(Name) 表现得更好.通过指定 COUNT(*)，您可以让优化器自由使用它希望使用的任何索引.通过指定 COUNT(Name)，您将强制查询引擎使用该表，或至少使用包含 NAME 列的索引.

COUNT(*) will also perform better than Count(Name). By specifying COUNT(*) you are leaving the optimizer free to use any index it wishes. By specifying COUNT(Name) you are forcing the query engine to use the table, or at least an index that contains the NAME column.

相关文章