MySQL Forums :: General :: Why isn't an index being used here (nested query)


Advanced Search

Why isn't an index being used here (nested query)
Posted by: Eric George ()
Date: October 06, 2009 05:11PM

Hi folks. I've run across something strange here. The original query:

SELECT catalog_num, date, id FROM catalog WHERE id IN (SELECT id FROM my_data WHERE run_id=16672) ORDER BY catalog_num;

This took forever - I killed it after a few minutes and ran DESCRIBE on it:

+----+--------------------+------------+------+----------------------+-------+---------+------------+----------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+------+----------------------+-------+---------+------------+----------+-----------------------------+
| 1 | PRIMARY | catalog | ALL | NULL | NULL | NULL | NULL | 73387403 | Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | my_data | ref | indx1,indx2,run_indx | indx2 | 8 | const,func | 1 | Using index |
+----+--------------------+------------+------+----------------------+-------+---------+------------+----------+-----------------------------+

So the primary query is not using an index at all, despite the fact that catalog.id is a unique index of this table??

I can run the nested query for the IDs independently and come up with the following equivalent primary query:

SELECT catalog_num, epoch, epoch_frac, elem_num, id FROM catalog WHERE id IN (73126020,73150731,73221736,73227796,73227840,73227974,73227978,73229616,73229627,73229789,73230017,73230130) ORDER BY catalog_num;

This executes in 0.21 seconds, and describe shows it using the catalog.id unique index.

So why won't it use the index when there is a nested query, but it will if you give it the equivalent query with the results of the sub-query substituted?

This is version 5.0.77 on a x86-64 Linux box

Thanks

Options: ReplyQuote


Subject Written By Posted
Why isn't an index being used here (nested query) Eric George 10/06/2009 05:11PM
Re: Why isn't an index being used here (nested query) Peter Brawley 10/06/2009 06:14PM
Re: Why isn't an index being used here (nested query) Eric George 10/06/2009 06:24PM


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.