Index is ignored

[arcadius]

I have 2 tables: small `a_datakind`, and big `data`, approx. 150GB (see attachment).

The query:
SELECT STRAIGHT_JOIN *
FROM data d
WHERE d.Data LIKE '%A223%'
AND d.DataKind
IN (
   SELECT dk.DataKindID
   FROM a_datakind dk
   WHERE dk.Transform = 11
);
.. runs inefficiently, EXPLAIN gives:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY d ALL NULL NULL NULL NULL 4835390 Using where
2 SUBQUERY dk ref PRIMARY,Transform Transform 5 const 6 Using index

Adding "FORCE INDEX (Index_data)" to "data d" has no effect.

Without "STRAIGHT_JOIN" (which required because original query joins some tables), index `Index_data` is used OK:

EXPLAIN SELECT *
FROM data d
WHERE d.Data LIKE '%A223%'
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 6 Using index
1 SIMPLE d ref Index_data Index_data 4 main.dk.DataKindID 268632 Using where

So, in production, I have to collect datakinds by additional query:

SELECT GROUP_CONCAT(DataKindID SEPARATOR ", ") FROM a_datakind WHERE Transform = 11;

.. and then use its output in the IN clause.

[Nilnandan Joshi (nilnandan-joshi)]

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;
+------------+----------+----------+----------+-----------------+----------------+------+-----...

[Nilnandan Joshi (nilnandan-joshi)]

With PS 5.5.40, getting same explain plan for both the queries.

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 | 7 | 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 | 7 | Using where |
| 2 | DEPENDENT SUBQUERY | dk | unique_subquery | PRIMARY,Transform | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-------+-----------------+-------------------+---------+---------+------+------+-------------+
2 rows in set (0.00 sec)

[Nilnandan Joshi (nilnandan-joshi)]

Hi,

This seems upstream bug (Oracle mysql). Do you want to report it from your side or want us to do it?
Can we use the same table structure there?

[arcadius (arcadius)]

Hi, I think you will do it better than me. You can use the same table stucture there.

[Nilnandan Joshi (nilnandan-joshi)]

Upstream bug reported. http://bugs.mysql.com/bug.php?id=75457

[Laurynas Biveinis (laurynas-biveinis)]

Upstream bug closed as a documentation bug. Do you agree with this resolution?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1597