Bug #69285 count upon not null field can not treated as count(*)
Submitted: 20 May 2013 3:48 Modified: 10 Jun 2013 10:26
Reporter: xiaobin lin (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5+ OS:Any
Assigned to: CPU Architecture:Any
Tags: count, covering index

[20 May 2013 3:48] xiaobin lin
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(*).
[10 Jun 2013 10:26] Umesh Shastry
Hello xiaobin,

Thank you for the report.

Regards,
Umesh