Bug #89481 mysql does't optimize range query when using where
Submitted: 31 Jan 2018 6:57 Modified: 2 Feb 2018 15:36
Reporter: z z Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.6.25.5.7.18 OS:Red Hat
Assigned to: CPU Architecture:Any

[31 Jan 2018 6:57] z z
Description:

when there is no index on column and query condition's result is false, mysql dose't optimize range query,but when there is a index on that column,mysql makes right choice and uses "no matching row in const table".

How to repeat:
 bai_test | CREATE TABLE `bai_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11756 DEFAULT CHARSET=utf8 

mysql> explain select * from bai_test where num<50 and num>3000000;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | bai_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1222816 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+

after i add a index on num
 create index idx_num on bai_test(num);
mysql> explain select * from bai_test where num<50 and num>3000000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
[31 Jan 2018 7:17] z z
5.6.25 5.7.18 all the same
[31 Jan 2018 7:18] z z
5.6.25
mysql> explain select * from baixyu where table_rows < 5 and table_rows>10;
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | baixyu | ALL  | NULL          | NULL | NULL    | NULL | 626889 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> create index idx_rows on baixyu(table_rows);
Query OK, 0 rows affected (2.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from baixyu where table_rows < 5 and table_rows>10;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                               |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
[2 Feb 2018 15:36] MySQL Verification Team
Hi,

Thanks for the report, good find! Detecting an impossible where should be possible without indices in this situation.

all best
Bogdan
[6 Feb 2018 11:38] Roy Lyseng
This is intentional, we only support range analysis for indexed columns.
But we can make it a feature request.