MySQL Forums
Forum List  »  Newbie

Subquery is real slow
Posted by: beggarstune
Date: November 28, 2006 06:26PM

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

Options: ReplyQuote


Subject
Written By
Posted
Subquery is real slow
November 28, 2006 06:26PM
November 28, 2006 11:37PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.