Question on bad execution plan

[Zw Zhu]

the mysql version is 5.1.39

a table letter with an index letter_receiver_id_receiver_del_is_read_id_desc( receiver_id, receiver_del, is_read, id desc ), the sql is explained as follow:

mysql> explain extended select id, date, sender_name, title, is_read, sender_id
-> from letter inner join (select id
-> from letter i force index (letter_receiver_id_receiver_del_is_read_id_desc)
-> where receiver_id = 3
-> and receiver_del = 0
-> order by is_read , id desc limit 0, 15) jt
-> using(id) \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: letter
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: jt.id
rows: 1
filtered: 100.00
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: i
type: ALL
possible_keys: letter_receiver_id_receiver_del_is_read_id_desc
key: letter_receiver_id_receiver_del_is_read_id_desc
key_len: 10
ref:
rows: 6
filtered: 50.00
Extra: Using filesort
3 rows in set, 1 warning (0.00 sec)

we can see that the inner query plan's type is full and the key column shows to use the index. i haven't seen the plan like this, what does it mean?

if i explain the inner query alone, the execution plan is another result, which is much better, why the first query doesn't use this plan? thank you.

mysql> explain extended select *
-> from letter i
-> where receiver_id = 3
-> and receiver_del = 0
-> order by is_read, id desc
-> limit 0, 15 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: i
type: ALL
possible_keys: letter_receiver_id_receiver_del_is_read_id_desc
key: NULL
key_len: NULL
ref: NULL
rows: 6
filtered: 50.00
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

Zhiwu Zhu



Edited 1 time(s). Last edit at 06/16/2010 11:49PM by Zw Zhu.