Slow query

Category: IPS Tracker - IP.Blog (for IP.Board)
Viewed: 9 times

Found this in the slow query logs. "Using where, using temporary, using filesort".

SELECT e.entry_id, e.entry_last_update, e.entry_name, e.blog_id, e.entry_name_seo, e.entry_author_id, e.entry_date,b.blog_name, b.blog_seo_name FROM ibf_blog_entries e LEFT JOIN ibf_blog_blogs b ON (
b.blog_id=e.blog_id ) WHERE b.blog_id IN (1174,2568) AND e.entry_status !='draft' AND b.blog_disabled = 0 AND b.blog_allowguests = 1 AND ( ( b.blog_owner_only=1 AND b.member_id=0 ) OR
b.blog_owner_only=0 ) AND ( b.blog_authorized_users LIKE '%,0,%' OR b.blog_authorized_users IS NULL ) ORDER BY e.entry_date DESC LIMIT 0,5;

We should try to optimize the query if possible. Some ideas (I'm not sure what triggers this query so I'm not sure what is feasible)

• We have the blog ids already. We could just pull the entrys from blog_entries without joining the blog table.
  
• Run two queries - one to get blog_id, and then one to get entries using an IN() clause with the blog ids. Will only work if expected number of blog ids will be low.
  
• Set some sort of "is retrievable" flag on entry or blog table to avoid all the combined where clauses.