MySQL Bugs: #10989: The subquery doesnt use the index type range

Bug #10989	The subquery doesnt use the index type range
Submitted:	31 May 2005 15:28	Modified:	6 Jul 2005 11:55
Reporter:	Mark Nozz	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	4.1.11, 6.0.7	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any
Tags:	performance, subquery

Description:
CREATE TABLE `matches` (
  `ID` int(11) NOT NULL auto_increment,
  `Home` int(11) NOT NULL,
  `Away` int(11) NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `players` (
  `ID` int(11) NOT NULL auto_increment,
  `Team` int(11) NOT NULL,
  `Deleted` int(11) NOT NULL,
  PRIMARY KEY  (`ID`),
  KEY `Team` (`Team`,`Deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `matches`(ID, Home, Away) VALUES (NULL, 1, 2);
INSERT INTO `players`(ID, Team) VALUES
(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),
(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),
(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3);

doesnt use the index Team:
SELECT ID, (SELECT GROUP_CONCAT(PL.ID) FROM players PL WHERE PL.Team IN (M.Home, M.Away) GROUP BY PL.Deleted) FROM matches M WHERE ID = 1;

In this case MySQL will not use index type range over the index Team.
If i put the numbers 1 and 2 instead of M.Home, M.Away the usage of index Team is possible (and in the large tables is used).

How to repeat:
Maybe its bug, or its underdone coz no one care about using more then one row in the subquery, i hope it will be fixed.

Hi,

There is still work being done optimizing subqueries. This is planned for later. I put this bug report on 'To be fixed later'.

Thanks for the report!

Best regards,
Geert

Still not fixed even in 6.0.7:

C:\Program Files\MySQL\MySQL Server 5.1\bin>mysql -P3311 -uroot -proot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 6.0.7-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `matches` (
    ->   `ID` int(11) NOT NULL auto_increment,
    ->   `Home` int(11) NOT NULL,
    ->   `Away` int(11) NOT NULL,
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.27 sec)

mysql>
mysql> CREATE TABLE `players` (
    ->   `ID` int(11) NOT NULL auto_increment,
    ->   `Team` int(11) NOT NULL,
    ->   `Deleted` int(11) NOT NULL,
    ->   PRIMARY KEY  (`ID`),
    ->   KEY `Team` (`Team`,`Deleted`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> INSERT INTO `matches`(ID, Home, Away) VALUES (NULL, 1, 2);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `players`(ID, Team) VALUES
    -> (NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),
    -> (NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),
    -> (NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3);
ERROR 1364 (HY000): Field 'Deleted' doesn't have a default value
mysql> alter table players modify `Deleted` int(11) NOT NULL DEFAULT 0;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `players`(ID, Team) VALUES
    -> (NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),(NULL, 1),
    -> (NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),(NULL, 2),
    -> (NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3),(NULL, 3);
Query OK, 21 rows affected (0.08 sec)
Records: 21  Duplicates: 0  Warnings: 0

mysql> explain SELECT ID, (SELECT GROUP_CONCAT(PL.ID) FROM players PL WHERE PL.T
eam IN (M.Home, M.Away)
    -> GROUP BY PL.Deleted) FROM matches M WHERE ID = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: M
         type: system
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra:
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: PL
         type: ALL
possible_keys: Team
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 21
        Extra: Using where; Using filesort
2 rows in set (0.22 sec)