Able to reproduce the same with above table structure and PS 5.6.21. 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>