| 2014-12-25 20:27:53 |
arcadius |
description |
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. |
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. |
|
