Why isn't an index being used here (nested query)

[Eric George]

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