Subselect query optimization problem

[Matthew Conway]

Hi All,

I'm trying to optimize a query, and am running into a problem.

Forms 1 and 2 below use the right indices and are fast, but form 3 does a full table scan. Unfortunately I need to use the 3rd form due to constraints fo a framework I'm using - the framework generates sql and I'm only allowed to provide the where clause. Is there any way I can force the 3rd form to use the correct indices and perform as well as the other two?


mysql> explain SELECT pictures.id FROM pictures WHERE pictures.id in (1, 2, 3, 4, 5) \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: pictures
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)




mysql> explain SELECT p1.id FROM pictures p1, (select id from pictures where id < 5) p2 where p1.id = p2.id \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: p1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: p2.id
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: pictures
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 4
Extra: Using where; Using index
3 rows in set (0.00 sec)




mysql> explain SELECT pictures.id FROM pictures WHERE pictures.id in (select id from pictures where id < 5) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: pictures
type: index
possible_keys: NULL
key: index_pictures_on_is_private
key_len: 2
ref: NULL
rows: 3766057
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: pictures
type: unique_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using index; Using where
2 rows in set (0.00 sec)