MySQL Forums
Forum List  »  Optimizer & Parser

Re: Question on bad execution plan
Posted by: Rick James
Date: June 28, 2010 08:50PM

It could be that the table is so small that the query optimizer is picking something "wrong".

Could we try these:
EXPLAIN
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 DESC,   -- changed this
                      id      desc
            limit  0, 15
      ) jt  using(id)
    order by  is_read , id desc
#  and...
EXPLAIN
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 ASC,
                      id      ASC   -- both ASC
            limit  0, 15
      ) jt  using(id)
    order by  is_read , id desc
I am not sure if it is smart enough to efficiently do one column ASC and the other DESC.

Options: ReplyQuote


Subject
Views
Written By
Posted
4426
June 16, 2010 07:36AM
1824
June 17, 2010 09:29AM
1556
June 21, 2010 09:24AM
1716
June 21, 2010 06:47PM
1770
June 22, 2010 07:33AM
1798
June 22, 2010 09:09AM
1666
June 23, 2010 07:44AM
1772
June 24, 2010 12:17AM
1783
June 24, 2010 09:17PM
1632
June 28, 2010 02:39AM
Re: Question on bad execution plan
1779
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.