MySQL Forums :: Optimizer & Parser :: Question on bad execution plan


Advanced Search

Question on bad execution plan
Posted by: Zw Zhu ()
Date: June 16, 2010 07:36AM

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.

Options: ReplyQuote


Subject Views Written By Posted
Question on bad execution plan 3630 Zw Zhu 06/16/2010 07:36AM
Re: Question on bad execution plan 1521 Rick James 06/17/2010 09:29AM
Re: Question on bad execution plan 1280 noicy seal 06/21/2010 09:24AM
Re: Question on bad execution plan 1448 Rick James 06/21/2010 06:47PM
Re: Question on bad execution plan 1548 noicy seal 06/22/2010 07:33AM
Re: Question on bad execution plan 1529 Rick James 06/22/2010 09:09AM
Re: Question on bad execution plan 1424 noicy seal 06/23/2010 07:44AM
Re: Question on bad execution plan 1453 Zw Zhu 06/24/2010 12:17AM
Re: Question on bad execution plan 1548 Rick James 06/24/2010 09:17PM
Re: Question on bad execution plan 1390 Zw Zhu 06/28/2010 02:39AM
Re: Question on bad execution plan 1487 Rick James 06/28/2010 08:50PM


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.