| Bug #75457 | Index is ignored with straight_join | ||
|---|---|---|---|
| Submitted: | 9 Jan 2015 5:41 | Modified: | 24 Feb 2016 15:01 |
| Reporter: | Nilnandan Joshi | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 5.6.21, 5.6.24, 5.7.6 | OS: | Any |
| Assigned to: | Paul DuBois | CPU Architecture: | Any |
[9 Jan 2015 6:53]
MySQL Verification Team
Hello Nilnandan Joshi, Thank you for the report and test case. Thanks, Umesh
[9 Jan 2015 6:56]
MySQL Verification Team
// 5.6.24 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.6.24 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.24-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux-glibc2.5 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +--------+----------+------------+------------+-----------------+ | DataID | DataKind | ParentMove | ParentData | Data | +--------+----------+------------+------------+-----------------+ | 6 | 6 | 400 | NULL | /home/nilnandan | | 7 | 6 | 400 | NULL | /home/nilnandan | | 8 | 6 | 500 | NULL | /home/nilnandan | | 9 | 6 | 700 | NULL | /home/nilnandan | +--------+----------+------------+------------+-----------------+ 4 rows in set (0.00 sec) mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+ | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | SUBQUERY | dk | ref | PRIMARY,Transform | Transform | 5 | const | 1 | Using index | +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+ | 1 | SIMPLE | dk | ref | PRIMARY,Transform | Transform | 5 | const | 1 | Using index | | 1 | SIMPLE | d | ref | Index_data | Index_data | 4 | test.dk.DataKindID | 1 | Using where | +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+ 2 rows in set (0.00 sec)
[9 Jan 2015 6:57]
MySQL Verification Team
// 5.7.6 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.7.6 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.7.6-m16-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | Linux | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +--------+----------+------------+------------+-----------------+ | DataID | DataKind | ParentMove | ParentData | Data | +--------+----------+------------+------------+-----------------+ | 6 | 6 | 400 | NULL | /home/nilnandan | | 7 | 6 | 400 | NULL | /home/nilnandan | | 8 | 6 | 500 | NULL | /home/nilnandan | | 9 | 6 | 700 | NULL | /home/nilnandan | +--------+----------+------------+------------+-----------------+ 4 rows in set (0.00 sec) mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+-------------+-------+------------+------+-------------------+-----------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+-----------+---------+-------+------+----------+-------------+ | 1 | PRIMARY | d | NULL | ALL | NULL | NULL | NULL | NULL | 9 | 11.11 | Using where | | 2 | SUBQUERY | dk | NULL | ref | PRIMARY,Transform | Transform | 5 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+-------------------+-----------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+-------------+-------+------------+------+-------------------+------------+---------+--------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-------------------+------------+---------+--------------------+------+----------+-------------+ | 1 | SIMPLE | dk | NULL | ref | PRIMARY,Transform | Transform | 5 | const | 1 | 100.00 | Using index | | 1 | SIMPLE | d | NULL | ref | Index_data | Index_data | 4 | test.dk.DataKindID | 1 | 11.11 | Using where | +----+-------------+-------+------------+------+-------------------+------------+---------+--------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec)
[9 Jan 2015 7:00]
Nilnandan Joshi
Originally reported here: https://bugs.launchpad.net/percona-server/+bug/1405677
[9 Jan 2015 7:00]
MySQL Verification Team
// 5.5.42 mysql> show variables like '%version%'; +-------------------------+---------------------------------------------------------+ | Variable_name | Value | +-------------------------+---------------------------------------------------------+ | innodb_version | 5.5.42 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.5.42-enterprise-commercial-advanced | | version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) | | version_compile_machine | x86_64 | | version_compile_os | linux2.6 | +-------------------------+---------------------------------------------------------+ 7 rows in set (0.00 sec) mysql> SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +--------+----------+------------+------------+-----------------+ | DataID | DataKind | ParentMove | ParentData | Data | +--------+----------+------------+------------+-----------------+ | 6 | 6 | 400 | NULL | /home/nilnandan | | 7 | 6 | 400 | NULL | /home/nilnandan | | 8 | 6 | 500 | NULL | /home/nilnandan | | 9 | 6 | 700 | NULL | /home/nilnandan | +--------+----------+------------+------------+-----------------+ 4 rows in set (0.00 sec) mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ 2 rows in set (0.00 sec)
[9 Jan 2015 7:07]
MySQL Verification Team
// 5.1.73 mysql> show variables like '%version%'; +-------------------------+------------------------------+ | Variable_name | Value | +-------------------------+------------------------------+ | protocol_version | 10 | | version | 5.1.73 | | version_comment | MySQL Community Server (GPL) | | version_compile_machine | x86_64 | | version_compile_os | unknown-linux-gnu | +-------------------------+------------------------------+ 5 rows in set (0.01 sec) mysql> SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +--------+----------+------------+------------+-----------------+ | DataID | DataKind | ParentMove | ParentData | Data | +--------+----------+------------+------------+-----------------+ | 6 | 6 | 400 | NULL | /home/nilnandan | | 7 | 6 | 400 | NULL | /home/nilnandan | | 8 | 6 | 500 | NULL | /home/nilnandan | | 9 | 6 | 700 | NULL | /home/nilnandan | +--------+----------+------------+------------+-----------------+ 4 rows in set (0.00 sec) mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where | +----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+ 2 rows in set (0.00 sec)
[9 Jan 2015 7:39]
Øystein Grøvlen
Posted by developer: This happens because STRAIGHT_JOIN will disable semi-join transformations. Changing this to a documentation bug since I cannot find anything about this in the manual. A list of requirements for semi-join transformations can be found here: http://roylyseng.blogspot.co.uk/2012/04/semi-join-in-mysql-56.html
[24 Feb 2016 15:01]
Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly. Mentioned that STRAIGHT_JOIN prevents index use in the semi-join discussion here: http://dev.mysql.com/doc/refman/5.7/en/subquery-optimization.html
Description: Index is ignored while using straight_join with Normal join it works fine. Please check "How to repeat" section for more info. How to repeat: mysql> show create table a_datakind \G *************************** 1. row *************************** Table: a_datakind Create Table: CREATE TABLE `a_datakind` ( `DataKindID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DataName` varchar(100) NOT NULL DEFAULT '', `DataDesc` text NOT NULL, `DataType` tinyint(3) unsigned NOT NULL, `ParentStageType` int(10) unsigned NOT NULL COMMENT 'Parent stage data refers to.', `ParentDataKind` int(10) unsigned DEFAULT NULL, `Sort` int(10) NOT NULL DEFAULT '0', `AllowMany` varchar(60) NOT NULL DEFAULT '' COMMENT 'Several data can be associated with move', `Readonly` varchar(100) NOT NULL DEFAULT '0' COMMENT 'Data are calculated at move / stage start.', `Required` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Data is required.', `onChange` varchar(100) NOT NULL DEFAULT '' COMMENT 'Javascript code', `onSave` varchar(100) NOT NULL DEFAULT '' COMMENT 'PHP code.', `Param` text NOT NULL, `Param2` varchar(100) NOT NULL DEFAULT '', `Param3` varchar(200) NOT NULL DEFAULT '', `DefValue` text NOT NULL COMMENT 'SQL code.', `Transform` int(10) unsigned DEFAULT NULL, PRIMARY KEY (`DataKindID`), KEY `FK_a_datakind_type` (`DataType`), KEY `FK_a_datakind_datakind` (`ParentDataKind`), KEY `FK_a_datakind_stage` (`ParentStageType`), KEY `Transform` (`Transform`), CONSTRAINT `FK_a_datakind_datakind` FOREIGN KEY (`ParentDataKind`) REFERENCES `a_datakind` (`DataKindID`) ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 ROW_FORMAT=DYNAMIC 1 row in set (0.00 sec) mysql> mysql> show create table data \G *************************** 1. row *************************** Table: data Create Table: CREATE TABLE `data` ( `DataID` int(10) unsigned NOT NULL AUTO_INCREMENT, `DataKind` int(10) unsigned NOT NULL DEFAULT '0', `ParentMove` int(10) unsigned NOT NULL DEFAULT '0', `ParentData` int(10) unsigned DEFAULT NULL, `Data` longblob NOT NULL, PRIMARY KEY (`DataID`), KEY `Index_data` (`DataKind`,`Data`(32)), KEY `FK_data_parentdata` (`ParentData`), KEY `FK_data_move` (`ParentMove`), CONSTRAINT `FK_data_datakind` FOREIGN KEY (`DataKind`) REFERENCES `a_datakind` (`DataKindID`), CONSTRAINT `FK_data_parentdata` FOREIGN KEY (`ParentData`) REFERENCES `data` (`DataID`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=cp1251 ROW_FORMAT=DYNAMIC 1 row in set (0.00 sec) mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',1); Query OK, 1 row affected, 3 warnings (0.01 sec) mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',10); Query OK, 1 row affected, 3 warnings (0.01 sec) mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',15); Query OK, 1 row affected, 3 warnings (0.01 sec) mysql> insert into a_datakind (DataName,DataDesc,DefValue,Transform) values ('abcded','asdkjasd','asdasd',11); Query OK, 1 row affected, 3 warnings (0.00 sec) mysql> select * from a_datakind; +------------+----------+----------+----------+-----------------+----------------+------+-----------+----------+----------+----------+--------+-------+--------+--------+----------+-----------+ | DataKindID | DataName | DataDesc | DataType | ParentStageType | ParentDataKind | Sort | AllowMany | Readonly | Required | onChange | onSave | Param | Param2 | Param3 | DefValue | Transform | +------------+----------+----------+----------+-----------------+----------------+------+-----------+----------+----------+----------+--------+-------+--------+--------+----------+-----------+ | 3 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 1 | | 4 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 10 | | 5 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 15 | | 6 | abcded | asdkjasd | 0 | 0 | NULL | 0 | | 0 | 0 | | | | | | asdasd | 11 | +------------+----------+----------+----------+-----------------+----------------+------+-----------+----------+----------+----------+--------+-------+--------+--------+----------+-----------+ 4 rows in set (0.00 sec) mysql> insert into data (DataKind,ParentMove,Data) values (3,100,'/home/nilnandan'); Query OK, 1 row affected (0.01 sec) mysql> insert into data (DataKind,ParentMove,Data) values (3,200,'/home/nilnandan'); Query OK, 1 row affected (0.01 sec) mysql> insert into data (DataKind,ParentMove,Data) values (3,300,'/home/nilnandan'); Query OK, 1 row affected (0.00 sec) mysql> insert into data (DataKind,ParentMove,Data) values (3,400,'/home/nilnandan'); Query OK, 1 row affected (0.01 sec) mysql> insert into data (DataKind,ParentMove,Data) values (5,400,'/home/nilnandan'); Query OK, 1 row affected (0.01 sec) mysql> insert into data (DataKind,ParentMove,Data) values (6,400,'/home/nilnandan'); Query OK, 1 row affected (0.01 sec) mysql> insert into data (DataKind,ParentMove,Data) values (6,400,'/home/nilnandan'); Query OK, 1 row affected (0.00 sec) mysql> insert into data (DataKind,ParentMove,Data) values (6,500,'/home/nilnandan'); Query OK, 1 row affected (0.01 sec) mysql> insert into data (DataKind,ParentMove,Data) values (6,700,'/home/nilnandan'); Query OK, 1 row affected (0.00 sec) mysql> mysql> mysql> SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +--------+----------+------------+------------+-----------------+ | DataID | DataKind | ParentMove | ParentData | Data | +--------+----------+------------+------------+-----------------+ | 6 | 6 | 400 | NULL | /home/nilnandan | | 7 | 6 | 400 | NULL | /home/nilnandan | | 8 | 6 | 500 | NULL | /home/nilnandan | | 9 | 6 | 700 | NULL | /home/nilnandan | +--------+----------+------------+------------+-----------------+ 4 rows in set (0.00 sec) mysql> EXPLAIN SELECT STRAIGHT_JOIN * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+ | 1 | PRIMARY | d | ALL | NULL | NULL | NULL | NULL | 9 | Using where | | 2 | SUBQUERY | dk | ref | PRIMARY,Transform | Transform | 5 | const | 1 | Using index | +----+-------------+-------+------+-------------------+-----------+---------+-------+------+-------------+ 2 rows in set (0.00 sec) mysql> EXPLAIN SELECT * FROM data d WHERE d.Data LIKE '%nil%' AND d.DataKind IN ( SELECT dk.DataKindID FROM a_datakind dk WHERE dk.Transform = 11 ); +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+ | 1 | SIMPLE | dk | ref | PRIMARY,Transform | Transform | 5 | const | 1 | Using index | | 1 | SIMPLE | d | ref | Index_data | Index_data | 4 | test.dk.DataKindID | 1 | Using where | +----+-------------+-------+------+-------------------+------------+---------+--------------------+------+-------------+ 2 rows in set (0.00 sec) mysql>