count(*) 和 count(other) 的区别

后来

--------------------------------------------------------------------------------------------------------------------------

MySQL innodb table当中，select count(A) from table where B ...  ， 我在B上建立了索引

当记录数小于173640 (173640 * 4 B = 694560 B,  我这里的B 是 4 bytes) 时，count(A) 和 count(*) 都走B的索引 

大于时，count(*) 都走B的索引 ， count(A) 走全表扫描，实际上count(*)和count(B) 在此等效

mysql> explain select count(A) from t1                 where B        > "2001-11-2" and B        < "2002-2-8";
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+-------------+
| id | select_type | table             | type  | possible_keys | key      | key_len | ref  | rows   | Extra       |
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+-------------+
|  1 | SIMPLE      | t1                | range | B             | B        | 8       | NULL | 172918 | Using where |
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+-------------+
1 row in set (0.00 sec)

range, key ==> 显然走了索引


mysql> explain select count(A) from t1                 where B        > "2001-11-2" and B        < "2002-2-9";
+----+-------------+-------------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | t1                | ALL  | B             | NULL | NULL    | NULL | 1000122 | Using where |
+----+-------------+-------------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

all ==> 显然走了索引 , 并且slow query 当中的Rows_examined 却是是 全表的行数，汗；select的时间也确实慢

--------------------------------------


mysql> explain select count(*) from t1                 where B        > "2001-11-2" and   B        < "2002-2-9";
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table             | type  | possible_keys | key      | key_len | ref  | rows   | Extra                    |
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1                | range | B             | B        | 8       | NULL | 173640 | Using where; Using index |
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)




mysql> explain select count(*) from t1                 where B        > "2001-11-2" and B        < "2002-2-29";
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+--------------------------+
| id | select_type | table             | type  | possible_keys | key      | key_len | ref  | rows   | Extra                    |
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | t1                | range | B             | B        | 8       | NULL | 199870 | Using where; Using index |
+----+-------------+-------------------+-------+---------------+----------+---------+------+--------+--------------------------+

1 row in set (0.00 sec)

两个range, key ==> 显然都走了索引

结论就是在这种情况下，不要自作聪明写count(A)，一时浅见，以后在研究出了什么问题