drop table if exists t; create table t (a serial) engine=innodb; insert t values (),(),(),(),(),(),(); insert t select null from t,t a,t b,t c,t d,t e limit 100000; optimize table t;analyze table t; select count(*), min(a), max(a) from t; explain select count(*) from t where a < 10; -- estimate select count(*) from t where a < 10; -- actual explain select count(*) from t where a < 100; -- estimate select count(*) from t where a < 100; -- actual explain select count(*) from t where a < 1000; -- estimate select count(*) from t where a < 1000; -- actual explain select count(*) from t where a < 10000; -- estimate <------ select count(*) from t where a < 10000; -- actual explain select count(*) from t where a < 20000; -- estimate <------ select count(*) from t where a < 20000; -- actual explain select count(*) from t where a < 40000; -- estimate <------ select count(*) from t where a < 40000; -- actual explain select count(*) from t where a < 200000;-- estimate <------- select count(*) from t where a < 200000; -- actual -------------------------------------------------------------------- mysql> drop table if exists t; Query OK, 0 rows affected (0.07 sec) mysql> create table t (a serial) engine=innodb; Query OK, 0 rows affected (0.06 sec) mysql> insert t values (),(),(),(),(),(),(); Query OK, 7 rows affected (0.01 sec) Records: 7 Duplicates: 0 Warnings: 0 mysql> insert t select null from t,t a,t b,t c,t d,t e limit 100000; Query OK, 100000 rows affected (34.76 sec) Records: 100000 Duplicates: 0 Warnings: 0 mysql> optimize table t;analyze table t; +--------+----------+----------+-------------------------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +--------+----------+----------+-------------------------------------------------------------------+ | test.t | optimize | note | Table does not support optimize, doing recreate + analyze instead | | test.t | optimize | status | OK | +--------+----------+----------+-------------------------------------------------------------------+ 2 rows in set (4.37 sec) +--------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------+---------+----------+----------+ | test.t | analyze | status | OK | +--------+---------+----------+----------+ 1 row in set (0.04 sec) mysql> select count(*), min(a), max(a) from t; +----------+--------+--------+ | count(*) | min(a) | max(a) | +----------+--------+--------+ | 100007 | 1 | 100007 | +----------+--------+--------+ 1 row in set (1.11 sec) mysql> explain select count(*) from t where a < 10; -- estimate +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | a | a | 8 | NULL | 9 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from t where a < 10; -- actual +----------+ | count(*) | +----------+ | 9 | +----------+ 1 row in set (0.00 sec) mysql> mysql> explain select count(*) from t where a < 100; -- estimate +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | a | a | 8 | NULL | 99 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from t where a < 100; -- actual +----------+ | count(*) | +----------+ | 99 | +----------+ 1 row in set (0.00 sec) mysql> mysql> explain select count(*) from t where a < 1000; -- estimate +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | a | a | 8 | NULL | 998 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from t where a < 1000; -- actual +----------+ | count(*) | +----------+ | 999 | +----------+ 1 row in set (0.02 sec) mysql> mysql> explain select count(*) from t where a < 10000; -- estimate <------ +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | a | a | 8 | NULL | 20224 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from t where a < 10000; -- actual +----------+ | count(*) | +----------+ | 9999 | +----------+ 1 row in set (0.17 sec) mysql> mysql> explain select count(*) from t where a < 20000; -- estimate <------ +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | a | a | 8 | NULL | 41676 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from t where a < 20000; -- actual +----------+ | count(*) | +----------+ | 19999 | +----------+ 1 row in set (0.30 sec) mysql> mysql> explain select count(*) from t where a < 40000; -- estimate <------ +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | a | a | 8 | NULL | 50184 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from t where a < 40000; -- actual +----------+ | count(*) | +----------+ | 39999 | +----------+ 1 row in set (0.65 sec) mysql> mysql> explain select count(*) from t where a < 200000;-- estimate <------- +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | t | NULL | range | a | a | 8 | NULL | 50184 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+-------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select count(*) from t where a < 200000; -- actual +----------+ | count(*) | +----------+ | 100007 | +----------+ 1 row in set (1.61 sec) mysql> mysql> select version(); +-----------------+ | version() | +-----------------+ | 5.7.5-m15-debug | +-----------------+ 1 row in set (0.00 sec) mysql>