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.