Description:
For the following query the filtering estimate is not based on histogram when index is not available:
mysql> explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode = 'CHN' or countrycode = 'IND';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 0.86 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
For the equivalent query using IN expression, the filtering estimate is based on histogram:
mysql> explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode IN ('CHN', 'IND');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 17.26 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
How to repeat:
Using the world database:
analyze table city update histogram on countrycode;
explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode = 'CHN' or countrycode = 'IND';
explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode IN ('CHN', 'IND');
Description: For the following query the filtering estimate is not based on histogram when index is not available: mysql> explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode = 'CHN' or countrycode = 'IND'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 0.86 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) For the equivalent query using IN expression, the filtering estimate is based on histogram: mysql> explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode IN ('CHN', 'IND'); +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | city | NULL | ALL | NULL | NULL | NULL | NULL | 4188 | 17.26 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) How to repeat: Using the world database: analyze table city update histogram on countrycode; explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode = 'CHN' or countrycode = 'IND'; explain select /*+ NO_INDEX(city) */ avg(population) from city where countrycode IN ('CHN', 'IND');