Bug #60185 index DEPENDENT SUBQUERY
Submitted: 20 Feb 2011 15:58 Modified: 20 Feb 2011 17:12
Reporter: ttt ttt Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.51, 5.5.11 OS:Linux
Assigned to: CPU Architecture:Any
Tags: filesort SUBQUERY
Triage: Needs Triage: D3 (Medium)

[20 Feb 2011 15:58] ttt ttt
Description:
EXPLAIN SELECT `a`, (SELECT `b` FROM `test5` as `alias` WHERE `alias`.`a`=`test5`.`a` ORDER BY `b` ASC LIMIT 1) as `param` FROM  `test5` GROUP BY `a`

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	PRIMARY 	test5 	range 	NULL 	a 	4 	NULL 	9 	Using index for group-by
2 	DEPENDENT SUBQUERY 	alias 	ref 	a 	a 	4 	func 	5 	Using where; Using index; Using filesort

In this case, mysql NEVER use full index(part1,part2) for sort, only part1 =>filesort

How to repeat:
CREATE TABLE `test5` (
`a` INT NOT NULL ,
`b` INT NOT NULL
) ENGINE = MYISAM ;
ALTER TABLE `test5` ADD INDEX ( `a` , `b` ) ;

INSERT INTO `test5` (`a`, `b`) VALUES ('2', '1'), ('2', '2'), ('2', '3'), ('2', '4'), ('2', '5'), ('2', '6'), ('2', '7'), ('2', '8'), ('2', '9'), ('2', '10'), ('2', '11'), ('2', '12'), ('2', '13'), ('2', '14'), ('2', '15'), ('2', '16'), ('2', '17'), ('2', '18'), ('2', '19'), ('2', '20')

INSERT INTO `test5` (`a`, `b`) VALUES ('1', '1'), ('1', '2'), ('1', '3'), ('1', '4'), ('1', '5'), ('1', '6'), ('1', '7'), ('1', '8'), ('1', '9'), ('1', '10'), ('1', '11'), ('1', '12'), ('1', '13'), ('1', '14'), ('1', '15'), ('1', '16'), ('1', '17'), ('1', '18'), ('1', '19'), ('1', '20')

SELECT `a`, (SELECT `b` FROM `test5` as `alias` WHERE `alias`.`a`=`test5`.`a` ORDER BY `b` ASC LIMIT 1) as `param` FROM  `test5` GROUP BY `a`

Suggested fix:
full index in SUBQUERY!
[20 Feb 2011 17:12] Valeriy Kravchuk
Indeed, even in current mysql-5.5 the plan is still the same:

macbook-pro:5.5 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 3
Server version: 5.5.11-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE `test5` (
    -> `a` INT NOT NULL ,
    -> `b` INT NOT NULL
    -> ) ENGINE = MYISAM ;
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER TABLE `test5` ADD INDEX ( `a` , `b` ) ;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO `test5` (`a`, `b`) VALUES ('2', '1'), ('2', '2'), ('2', '3'), ('2', '4'),
    -> ('2', '5'), ('2', '6'), ('2', '7'), ('2', '8'), ('2', '9'), ('2', '10'), ('2', '11'),
    -> ('2', '12'), ('2', '13'), ('2', '14'), ('2', '15'), ('2', '16'), ('2', '17'), ('2',
    -> '18'), ('2', '19'), ('2', '20')
    -> ;
Query OK, 20 rows affected (0.02 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `test5` (`a`, `b`) VALUES ('1', '1'), ('1', '2'), ('1', '3'), ('1', '4'),
    -> ('1', '5'), ('1', '6'), ('1', '7'), ('1', '8'), ('1', '9'), ('1', '10'), ('1', '11'),
    -> ('1', '12'), ('1', '13'), ('1', '14'), ('1', '15'), ('1', '16'), ('1', '17'), ('1',
    -> '18'), ('1', '19'), ('1', '20')
    -> ;
Query OK, 20 rows affected (0.00 sec)
Records: 20  Duplicates: 0  Warnings: 0

mysql> explain SELECT `a`, (SELECT `b` FROM `test5` as `alias` WHERE `alias`.`a`=`test5`.`a` ORDER BY
    -> `b` ASC LIMIT 1) as `param` FROM  `test5` GROUP BY `a`
    -> 
    -> ;
+----+--------------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
+----+--------------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
|  1 | PRIMARY            | test5 | range | NULL          | a    | 4       | NULL |    9 | Using index for group-by                 |
|  2 | DEPENDENT SUBQUERY | alias | ref   | a             | a    | 4       | func |    5 | Using where; Using index; Using filesort |
+----+--------------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
2 rows in set (0.39 sec)

For this specific case workaround is possible I'd say:

mysql> explain SELECT `a`, (SELECT MIN(`b`) FROM `test5` as `alias` WHERE `alias`.`a`=`test5`.`a`) as `param` FROM  `test5` GROUP BY `a`;+----+--------------------+-------+-------+---------------+------+---------+------+------+--------------------------+
| id | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |
+----+--------------------+-------+-------+---------------+------+---------+------+------+--------------------------+
|  1 | PRIMARY            | test5 | range | NULL          | a    | 4       | NULL |    9 | Using index for group-by |
|  2 | DEPENDENT SUBQUERY | alias | ref   | a             | a    | 4       | func |    5 | Using index              |
+----+--------------------+-------+-------+---------------+------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)

P.S. Even with more specific reference to `b` from subquery, plan is the same:

mysql> explain SELECT `a`, (SELECT `b` FROM `test5` as `alias` force index(a) WHERE `alias`.`a`=`test5`.`a` ORDER BY `alias`.`b` ASC LIMIT 1) as `param` FROM  `test5` GROUP BY `a`;
+----+--------------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
| id | select_type        | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                                    |
+----+--------------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
|  1 | PRIMARY            | test5 | range | NULL          | a    | 4       | NULL |    9 | Using index for group-by                 |
|  2 | DEPENDENT SUBQUERY | alias | ref   | a             | a    | 4       | func |    5 | Using where; Using index; Using filesort |
+----+--------------------+-------+-------+---------------+------+---------+------+------+------------------------------------------+
2 rows in set (0.00 sec)
[26 Dec 2013 3:28] David Marcus
See the thread

http://forums.mysql.com/read.php?115,600241,600241#msg-600241

for similar examples.