MySQL Forums
Forum List  »  Optimizer & Parser

Re: Question on bad execution plan
Posted by: Zw Zhu
Date: June 24, 2010 12:17AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
4449
June 16, 2010 07:36AM
1840
June 17, 2010 09:29AM
1576
June 21, 2010 09:24AM
1724
June 21, 2010 06:47PM
1789
June 22, 2010 07:33AM
1814
June 22, 2010 09:09AM
1673
June 23, 2010 07:44AM
Re: Question on bad execution plan
1784
June 24, 2010 12:17AM
1805
June 24, 2010 09:17PM
1641
June 28, 2010 02:39AM
1788
June 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.