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: | |
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
[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.