MySQL Forums
Forum List  »  Optimizer & Parser

Re: Distinct/group by, order by and filesort
Posted by: Tolan Blundell
Date: February 24, 2010 04:50AM

Actually I have a simpler example. In this case the distinct isn't needed, but it can't actually be removed because the complex example above is the real requirement but this demonstrates the problem more simply:



SELECT DISTINCT( article.id ) as TD, article.*, press_release.* FROM article 

LEFT OUTER JOIN press_release ON ( article.id=press_release.id )  

WHERE 
    (article.published = 1) 
    AND ( article.TORM_IS_CURRENT_REVISION = 1 ) 


ORDER BY article.article_date;


visual explain output:


Filesort
+- TEMPORARY
   table          temporary(article,press_release)
   +- JOIN
      +- Bookmark lookup
      |  +- Table
      |  |  table          press_release
      |  |  possible_keys  pr_composite
      |  +- Index lookup
      |     key            press_release->pr_composite
      |     possible_keys  pr_composite
      |     key_len        4
      |     ref            my_db.article.id
      |     rows           3
      +- Filter with WHERE
         +- Bookmark lookup
            +- Table
            |  table          article
            |  possible_keys  article_published,art_is_current_rev,art_comp_x3,art_comp_x4,acx10,acx12,acx15
            +- Index lookup
               key            article->acx10
               possible_keys  article_published,art_is_current_rev,art_comp_x3,art_comp_x4,acx10,acx12,acx15
               key_len        4
               ref            const,const
               rows           7933

and the acx10 key:

create index acx10 on article( published,TORM_IS_CURRENT_REVISION,article_date,id);



Edited 1 time(s). Last edit at 02/24/2010 05:00AM by Tolan Blundell.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Distinct/group by, order by and filesort
2702
February 24, 2010 04:50AM


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.