Bug #32341 Subquery does not use index with session variable, works with string literal
Submitted: 13 Nov 2007 19:29 Modified: 16 Oct 2008 14:35
Reporter: Devananda van der Veen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.41, 5.0.52 OS:Linux
Assigned to:
Tags: index optimization, session variable, subquery
Triage: Triaged: D3 (Medium)

[13 Nov 2007 19:29] Devananda van der Veen
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)
[13 Nov 2007 19:31] Devananda van der Veen
SQL dump of table structures and data

Attachment: example.sql (application/octet-stream, text), 2.65 KiB.

[8 Dec 2007 19:19] Valerii Kravchuk
Thank you for a bug report. Verified just as described with 5.0.52.
[22 Jan 2008 21:17] Sergey Petrunya
The query should be handled by materialization strategy (WL#1110), but at the moment it is not.
[22 Jan 2008 22:01] Sergey Petrunya
.. because NOT IN requires NULL-aware subquery handling (see http://forge.mysql.com/worklog/task.php?id=3830), which is not yet implemented.
[22 Jan 2008 22:03] Sergey Petrunya
Please disregard the last comment (but the one before it), posted to the wrong bug.
[26 Jun 2008 15:57] Sergey Petrunya
The problem is that use of a session variable makes MySQL assume that subquery is "uncacheable", even if the said variable does not change during the query execution. 

We intend to fix this at some point, but not in MySQL 5.x. At the moment the target version is MySQL 6.x
[26 Dec 2010 14:00] Valerii Kravchuk
Bug #18656 was marked as a duplicate of this one.