I have the following query
select ps.id, ps.title , IFNULL(s.likes,0) as num_likes , IFNULL(s.comments,0) as num_comments , IFNULL(s.ratings,0) as num_ratings , IFNULL(s.views,0) as num_views , IFNULL(s.avg_rating,0) as avg_rating from ps left outer join ( select presiid, sum(views) as views, sum(likes) as likes, sum(ratings) as ratings, avg(ratings) as avg_rating, sum(downloads) as downloads, sum(comments) as comments, sum(embeds) as embeds, sum(shares) as shares from tblstatistics group by presiid ) s on s.presiid = ps.id where ps.active = 1 order by datepublished desc LIMIT 0, 12
Running EXPLAIN on the above shows this
1 PRIMARY ps ref active active 1 const 402 Using temporary; Using filesort 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 334 2 DERIVED tblstatistics ALL NULL NULL NULL NULL 2643 Using temporary; Using filesort
As you can see this is not good....tblstatistics is not using any indexes and is scanning the entire table. How can I optimize this query and make it use indexes? There are over 100,000 rows in the DB on the production DB where this query runs.
Any help will be appreciated....