MySQL Bugs: #61517: loose index scan not done when LIMIT provided

Bug #61517	loose index scan not done when LIMIT provided
Submitted:	14 Jun 2011 18:32	Modified:	29 Oct 2018 18:01
Reporter:	Domas Mituzas	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.1, 5.5	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
if LIMIT clause is provided, loose index scan is not done for a query

How to repeat:
mysql> explain select max(c) from inno group by b;
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows   | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
|  1 | SIMPLE      | inno  | range | NULL          | bc   | 5       | NULL | 108328 | Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)

mysql> explain select max(c) from inno group by b limit 10;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | inno  | index | NULL          | bc   | 10      | NULL |   10 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql>  select max(c) from inno group by b limit 10;
+--------+
| max(c) |
+--------+
|    999 |
|    999 |
|    999 |
|    999 |
|    999 |
|    999 |
|    999 |
|    999 |
|    999 |
|    999 |
+--------+
10 rows in set (0.00 sec)

mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 2     |
| Handler_read_last          | 0     |
| Handler_read_next          | 9999  |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
16 rows in set (0.00 sec)

CREATE TABLE `inno` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `bc` (`b`,`c`)
) 

Suggested fix:
use loose index scan if LIMIT is provided :)

Thank you for the bug report. Verified with current mysql-5.1 on Mac OS X:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.59-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `inno` (   `a` int(11) NOT NULL AUTO_INCREMENT,   `b` int(11) DEFAULT NULL,   `c` int(11) DEFAULT NULL,   PRIMARY KEY (`a`),   KEY `bc` (`b`,`c`) );
Query OK, 0 rows affected (0.45 sec)

mysql> explain select max(c) from inno group by b;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | inno  | range | NULL          | bc   | 5       | NULL |   10 | Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.07 sec)

mysql> explain select max(c) from inno group by b limit 3;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | inno  | index | NULL          | bc   | 10      | NULL |   71 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> select max(c) from inno group by b;
+--------+
| max(c) |
+--------+
|      0 |
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
+--------+
10 rows in set (0.00 sec)

mysql> analyze table inno;
+-----------+---------+----------+----------+
| Table     | Op      | Msg_type | Msg_text |
+-----------+---------+----------+----------+
| test.inno | analyze | status   | OK       |
+-----------+---------+----------+----------+
1 row in set (0.03 sec)

mysql> explain select max(c) from inno group by b limit 3;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | inno  | index | NULL          | bc   | 10      | NULL |  239 | Using index |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select max(c) from inno group by b;
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | SIMPLE      | inno  | range | NULL          | bc   | 5       | NULL |   10 | Using index for group-by |
+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

This might have been fixed in 5.6 or 5.7? Can't repro on 5.7.13

seems to be closed at some point in time