| Bug #18182 | indexed ORDER BY should be possible on joined tables | ||
|---|---|---|---|
| Submitted: | 13 Mar 2006 9:32 | Modified: | 3 Nov 2008 16:39 |
| Reporter: | Domas Mituzas | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S4 (Feature request) |
| Version: | 5.1-bk | OS: | Linux (Linux, MacOSX) |
| Assigned to: | CPU Architecture: | Any | |
[13 Nov 2007 17:15]
Valeriy Kravchuk
Thank you for a reasonable feature request.
[3 Nov 2008 16:32]
Valeriy Kravchuk
The problem looks fixed now:
C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -P3310 -uroot -proot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.29-rc-community-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `entries` (
-> `c` int(11) NOT NULL auto_increment,
-> `a` int(11) default NULL,
-> `d` varchar(255) default NULL,
-> PRIMARY KEY (`c`),
-> KEY `a` (`a`,`c`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.67 sec)
mysql> insert into entries (`a`, `d`) values (1, 'a'), (2, 'b'), (3, 'c');
Query OK, 3 rows affected (0.13 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE `consttable` (
-> `a` int(11) NOT NULL auto_increment,
-> `b` varchar(255) default NULL,
-> PRIMARY KEY (`a`),
-> UNIQUE KEY `b` (`b`)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.16 sec)
mysql> alter table consttable engine=InnoDB;
Query OK, 0 rows affected (0.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into consttable values (1, 'a'), (2, 'b'), (3, 'c');
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> explain select d from consttable natural join entries where b='b' order b
y b,c;
+----+-------------+------------+-------+---------------+------+---------+------
-+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref
| rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------
-+------+-------------+
| 1 | SIMPLE | consttable | const | PRIMARY,b | b | 768 | const
| 1 | Using index |
| 1 | SIMPLE | entries | ref | a | a | 5 | const
| 1 | Using where |
+----+-------------+------------+-------+---------------+------+---------+------
-+------+-------------+
2 rows in set (0.13 sec)
Am I missing something?
[3 Nov 2008 16:34]
Domas Mituzas
It is fixed in 5.1, not in 5.0 though. But I guess it won't be, right? :)
[3 Nov 2008 16:39]
Valeriy Kravchuk
Seems fixed in 5.1 already.
Description: ORDER BY should use indexes on joined tables. In examples below if ORDER BY is omitted, rows are returned in ordered state (as indexes might be read anyway), but ORDER BY forces filesorts. How to repeat: mysql> show create table consttable \G *************************** 1. row *************************** Table: consttable Create Table: CREATE TABLE `consttable` ( `a` int(11) NOT NULL auto_increment, `b` varchar(255) default NULL, PRIMARY KEY (`a`), UNIQUE KEY `b` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> show create table entries \G *************************** 1. row *************************** Table: entries Create Table: CREATE TABLE `entries` ( `c` int(11) NOT NULL auto_increment, `a` int(11) default NULL, `d` varchar(255) default NULL, PRIMARY KEY (`c`), KEY `a` (`a`,`c`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec) mysql> explain select d from consttable natural join entries where b='test' order by b,c; +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | consttable | ref | PRIMARY,b | b | 258 | const | 1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | a | a | 5 | test.consttable.a | 1 | Using where | +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> explain select d from consttable natural join entries where b='test' order by a,c; +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | consttable | ref | PRIMARY,b | b | 258 | const | 1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | a | a | 5 | test.consttable.a | 1 | Using where | +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ 2 rows in set (0.00 sec) mysql> explain select d from consttable natural join entries where b='test' order by c; +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ | 1 | SIMPLE | consttable | ref | PRIMARY,b | b | 258 | const | 1 | Using where; Using index; Using temporary; Using filesort | | 1 | SIMPLE | entries | ref | a | a | 5 | test.consttable.a | 1 | Using where | +----+-------------+------------+------+---------------+------+---------+-------------------+------+-----------------------------------------------------------+ 2 rows in set (0.00 sec) Suggested fix: Existing indexes should be used for sorting joined tables, if ORDER BY is specified.