List:General Discussion« Previous MessageNext Message »
From:Walter Heck Date:January 14 2009 9:45am
Subject:Re: How to avoid Using temporary; Using filesort
View as plain text  
One optimization I see quickly is changing the left join to an inner join.
You always look for records that exist in tag_ad_map (by checking for
tm.is_active) so the left join is not necessary. That should at least speed
this query up considerably.

Walter

Need MySQL advice?
OlinData.com is the place to go! (http://www.olindata.com)


SELECT COUNT(*) antal,ad.ad_id FROM ad
>
> LEFT JOIN tag_ad_map tm ON tm.ad_id=ad.ad_id
>
> WHERE
>
> tm.tag_id IN (99, 10807, 20728, 21, 135)
>
> AND ad.is_removed = 0 AND ad.is_active=1 AND (ad.ant_feedback_alert <= 5 OR
> ad.whitelisted = 1)
>
> AND tm.is_active=1 AND ad.ad_id != 32793
>
> GROUP BY tm.ad_id ORDER BY antal DESC LIMIT 10
>
>

Thread
How to avoid Using temporary; Using filesortJohan Thorvaldsson14 Jan
  • Re: How to avoid Using temporary; Using filesortWalter Heck14 Jan
    • Re: How to avoid Using temporary; Using filesortJohan Thorvaldsson14 Jan