Description:
In some cases when counting rows using where statement，count(*) can use covering index, but count a *not null row* can not.
I put an example bellow, using mysiam engine.
If InnoDB is used, count(auci) can use Covering index too, but count(gmt) will not.
This will lead to more response time.
The root reason is that when optimizing, the field is checked whether included in the fields of the index.
The "count(field)" shoule be different from "sum(field)" or "count(distinct field)".
How to repeat:
| my_data | CREATE TABLE `my_data` (
`auci` bigint(20) NOT NULL,
`tit` varchar(256) DEFAULT NULL,
`gmt` datetime NOT NULL,
`sta` datetime DEFAULT NULL,
`pic` varchar(256) DEFAULT NULL,
PRIMARY KEY (`auci`),
KEY `gmt_poi` (`gmt`),
KEY `sta` (`sta`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain select count(*) from my_data where sta>='2011-7-10';
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
| 1 | SIMPLE | my_data | range | sta | sta | 6 | NULL | 437463 | Using where; Using index |
+----+-------------+---------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select count(auci) from my_data where sta>='2011-7-10';
+----+-------------+---------+-------+---------------+------+---------+------+--------+----------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+------+---------+------+--------+----------------------------------+
| 1 | SIMPLE | my_data | range | sta | sta | 6 | NULL | 437463 | Using index condition; Using MRR |
+----+-------------+---------+-------+---------------+------+---------+------+--------+----------------------------------+
1 row in set (0.00 sec)
mysql> select count(*) from my_data where sta>='2011-7-10';
+----------+
| count(*) |
+----------+
| 378479 |
+----------+
1 row in set (0.19 sec)
mysql> select count(auci) from my_data where sta>='2011-7-10';
+-------------+
| count(auci) |
+-------------+
| 378479 |
+-------------+
1 row in set (0.99 sec)
Suggested fix:
Consider the meaning of count(field), if the field is defined as "NOT NULL", it can be treated as count(*).
