Optimize Query with Derived Table

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   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....

Your left outer join looks to be condensing your 100,000 rows in tblstatistics into approximately 2,643:

2   DERIVED tblstatistics   ALL NULL    NULL    NULL    NULL    *2643*    Using temporary; Using filesort

and then it places that grouping in into a temporary table. At this point, it applies the aggregate functions (SUM, AVG, etc).

Unfortunately, since you are not limiting your tblstatistics with a WHERE statement, this is probably the best you can do.

Overall, the only limiting you do is on ps.active=1. This is probably a really low cardinality column, and if it's an index, it provides no benefit as MySQL will ignore it anyway.

StackExchange Database Administrators Q#12123, answer score: 2