MySQL Forums
Forum List  »  Optimizer & Parser

Re: Question on bad execution plan
Posted by: Rick James
Date: June 17, 2010 09:29AM

The isolated inner query is different in two important ways:
* it does not have
force index (letter_receiver_id_receiver_del_is_read_id_desc)
* it calls for "SELECT *", which requires it to fetch fields other than what is in the index.

What percentage of the rows satisfy
where receiver_id = 3 and receiver_del = 0
? If it is more than 10-30%, then the optimizer will decide to do a table scan rather than bouncing between the index and the data.

SHOW TABLE STATUS LIKE 'letter'\G
SHOW CREATE TABLE letter \G

Recommend you remove all uses of USE/FORCE INDEX().

This may be the optimal:
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   -- Needed for dups in 'is_read'?
            limit  0, 15
      ) jt  using(id)
    order by  is_read , id desc
The subquery will be "Using index"; the outer one will do 15 probes into the table, plus a using temporary and filesort, but this is not critical for 15 rows. And, if you have not TEXT fields, it might do it using a MEMORY table, hence, entirely in RAM.

Options: ReplyQuote


Subject
Views
Written By
Posted
4449
June 16, 2010 07:36AM
Re: Question on bad execution plan
1839
June 17, 2010 09:29AM
1576
June 21, 2010 09:24AM
1723
June 21, 2010 06:47PM
1789
June 22, 2010 07:33AM
1814
June 22, 2010 09:09AM
1673
June 23, 2010 07:44AM
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.