Description:
The optimizer is incorrectly not using an index when performing a range-scan in a subquery with ORDER BY indexed_column ASC LIMIT 1 to retrieve the smallest record greater than a given value, when the const value is a session variable. It correctly uses the index if a JOIN is used in place of the subquery, and when the session variable is replaced with a string literal.
I have attached four SQL explain statements showing three cases that work as expected and one that does not. I have also attached the SQL dump of the test data I used.
How to repeat:
mysql> set @ip=2000001;
Query OK, 0 rows affected (0.00 sec)
mysql> explain select b.name from geo_ip AS a LEFT JOIN geo_region AS b ON a.region_id=b.region_id WHERE a.ip_to >= 2000001 ORDER BY a.ip_to ASC LIMIT 1;
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| 1 | SIMPLE | a | range | ip_to | ip_to | 8 | NULL | 2 | Using where |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.region_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)
mysql> explain select b.name from geo_ip AS a LEFT JOIN geo_region AS b ON a.region_id=b.region_id WHERE a.ip_to >= @ip ORDER BY a.ip_to ASC LIMIT 1;
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
| 1 | SIMPLE | a | range | ip_to | ip_to | 8 | NULL | 2 | Using where |
| 1 | SIMPLE | b | eq_ref | PRIMARY | PRIMARY | 4 | test.a.region_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+------------------+------+-------------+
2 rows in set (0.00 sec)
mysql> explain select name from geo_region where region_id=(select region_id from geo_ip where ip_to >= 2000001 order by ip_to asc limit 1);
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | geo_region | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | geo_ip | range | ip_to | ip_to | 8 | NULL | 2 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.01 sec)
mysql> explain select name from geo_region where region_id=(select region_id from geo_ip where ip_to >= @ip order by ip_to asc limit 1);
+----+----------------------+------------+-------+---------------+-------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+----------------------+------------+-------+---------------+-------+---------+------+------+-------------+
| 1 | PRIMARY | geo_region | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | UNCACHEABLE SUBQUERY | geo_ip | range | ip_to | ip_to | 8 | NULL | 2 | Using where |
+----+----------------------+------------+-------+---------------+-------+---------+------+------+-------------+
2 rows in set (0.00 sec)
