Playing with count(*) optimizer work

Article about bug report #68814 related to testing count(*) explain plan.
Our sales table huge enough to play with.

mysql> select count(*) from sales; 
+----------+ 
| count(*) | 
+----------+ 
| 2500003 | 
+----------+ 
1 row in set (0.56 sec)

First with regular count(*) without where clause:

mysql> explain select count(*) from salesG 
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: index 
possible_keys: NULL 
key: sales_cust_idx 
key_len: 4 
ref: NULL 
rows: 2489938 
Extra: Using index 1 row in set (0.00 sec) 

Estimated rows -> rows: 2489938 Then with {where sales_id > 0}:

mysql> explain select count(*) from sales where sales_id > 0G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: range 
possible_keys: PRIMARY 
key: PRIMARY 
key_len: 4 
ref: NULL 
rows: 1244969 
Extra: Using where; Using index 1 row in set (0.00 sec)

Estimated rows -> rows: 1244969 -> so there is difference between query with {sales_id > 0} and with no clause.

Another one with {where sales_id > 1800000}:

mysql> explain select count(*) from sales where sales_id > 1800000G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: range 
possible_keys: PRIMARY 
key: PRIMARY 
key_len: 4 
ref: NULL 
rows: 1244969 
Extra: Using where; Using index 
1 row in set (0.00 sec) 

Estimated rows -> rows: 1244969 So there is no difference between {sales_id > 1800000} and {sales_id > 0} (by mean of explain plan and estimated rows)

Another interesting thing:

-- 1 
mysql> explain select count(*) from sales where sales_id >0 or sales_id <0G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: range 
possible_keys: PRIMARY 
key: PRIMARY 
key_len: 4 
ref: NULL 
rows: 1244970 
Extra: Using where; Using index 
1 row in set (0.00 sec)

Estimated rows -> rows: 1244969 + 1

-- 2 
mysql> explain select count(*) from sales where sales_id >0 or sales_id <=0G
*************************** 1. row *************************** 
id: 1 
select_type: SIMPLE 
table: sales 
type: index 
possible_keys: PRIMARY 
key: sales_cust_idx 
key_len: 4 
ref: NULL 
rows: 2489938 
Extra: Using where; Using index 
1 row in set (0.00 sec) 

Estimated rows: 2489938

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s