Comment 0 for bug 1405677

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 bacause 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.