I have the following MySQL query that takes more than a day to execute:
SELECT SN,NUMBER FROM a
WHERE SN IN
(SELECT LOWER_SN FROM b
WHERE HIGHER_ED LIKE "%c1" AND LOWER_ED LIKE "%16")
AND ED LIKE "%16"
The subquery takes 21 seconds to run and returns 11035 rows. I have indices on a:
SHOW INDEX FROM a
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
0 a 1 wob1 1 ED A 756095 None None BTREE
1 a 1 wob2 1 SN A 2268287 None None BTREE
2 a 1 wob3 1 ED A 756095 None None BTREE
3 a 1 wob3 2 SN A 9073150 None None BTREE
4 a 1 wob4 1 NUMBER A 18146301 None None YES BTREE
5 a 1 wob5 1 SN A 2268287 None None BTREE
6 a 1 wob5 2 NUMBER A 18146301 None None YES BTREE
EXPLAIN
gives:
# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'PRIMARY', 'a', 'ALL', NULL, NULL, NULL, NULL, '18146301', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'b', 'index_subquery', 'cfg2', 'cfg2', '47', 'func', '6', 'Using where'
Why doesn't it use the indices? How can i speedup the query?
%
cannot use indexes, as strings are indexed from their starting characters. This might be one of the rarer cases where correlated subqueries can actually improve performance. – Uueerdo