Subquery is real slow
These are real quick - I have indexes on all fields involved. 'id' is primary, auto-increment key for both table word and paragraph_word.
SELECT id FROM word WHERE word_str IN ("mike","jen"); -- Returns 5722,5556
SELECT id FROM paragraph_word WHERE word_id IN (5722,5556);
This is the previous two SELECTs combined and it does a complete table scan -- takes forever.
SELECT id FROM paragraph_word WHERE word_id IN (SELECT id FROM word WHERE word_str IN ("mike","jen"));
EXPLAIN SELECT id FROM paragraph_word WHERE word_id IN (5722,5556);
1, 'SIMPLE', 'paragraph_word', 'range', 'IDX_word_id', 'IDX_word_id', '4', '', 940, 'Using where'
EXPLAIN SELECT id FROM paragraph_word WHERE word_id IN (SELECT id FROM word WHERE word_str IN ("mike","jen"));
1, 'PRIMARY', 'paragraph_word', 'ALL', '', '', '', '', 2883002, 'Using where'
2, 'DEPENDENT SUBQUERY', 'word', 'unique_subquery', 'PRIMARY,IDX_word_str', 'PRIMARY', '4', 'func', 1, 'Using index; Using where'
How do I rewrite my query to make it fast, please?
- Mike