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