Re: Question on bad execution plan

[Zw Zhu]

I change the query, and get the following execution plan:

mysql> explain extended select id, date, sender_name, title, is_read, sender_id
-> from letter inner join (select id
-> from letter i
-> where receiver_id = 3
-> and receiver_del = 0
-> order by is_read , id desc limit 0, 15) jt
-> using(id) order by is_read , id desc \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: letter
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
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: 26
filtered: 3.85
Extra: Using filesort
3 rows in set, 1 warning (0.00 sec)

The execution plan shows that the inner query doesn't 'Using index' and scan all the 26 rows, even the filtered is 3.85.

Thanks.
zhiwu Zhu