MySQL optimization - use of index

Question

I have the following INDEX on table 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

a, '1', 'oc_id', '1', 'OC_ID', 'A', '19', NULL, NULL, 'YES', 'BTREE', '', ''

And the explain plan shows it's being used:

EXPLAIN SELECT * FROM a
WHERE OC_ID IN (
                5841)

'1', 'SIMPLE', a, 'ref', 'oc_id,oc1', 'oc_id', '6', 'const', '121080', 'Using where'

But using a subquery it's not used:

EXPLAIN SELECT * FROM a
WHERE OC_ID IN (SELECT OC_ID FROM b WHERE tteci = "54301")

'1', 'PRIMARY', a, 'ALL', NULL, NULL, NULL, NULL, '701145408', 'Using where'
'2', 'DEPENDENT SUBQUERY', b, 'index_subquery', 'OC_ID,tteci1', 'OC_ID', '6', 'func', '1', 'Using where'

What am I doing wrong?

The first step is to eliminate the subquery by replacing it with a join (hint: join a and b on OC_ID and filter b on tteci). Then either 1) MySQL will "be happy" and select the desired index, or 2) the question will be different as the subquery is no longer relevant / existing. — user2864740
Something does not make sense; please provide SHOW CREATE TABLE. — Rick James

scaisEdge scaisEdge · Accepted Answer · 2018-06-29T19:06:02

for better performance you could use a join instead of an IN clause based on a subselect result

SELECT * 
FROM a
INNER  JOIN b ON a.OC_ID = b.OC_ID and b.tteci =  "54301"

for index you should use an composite one with the columns involved in the JOIN clause eg for table b :

(tteci, OC_ID)

for table a the OC_ID index is enough

MySQL optimization - use of index

1 Answers