Optimize Query with Derived Table

Question

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

Answer 1

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.

Answer 2

You can try to rewrite your query so it calculates the aggregations after the LEFT JOIN, and see if that gives you a different execution plan (but the optimizer can choose to ignore that).

SELECT ps.id, 
       ps.title, 
       IFNULL(SUM(s.likes), 0)    AS num_likes, 
       IFNULL(SUM(s.comments), 0) AS num_comments, 
       IFNULL(SUM(s.ratings), 0)  AS num_ratings, 
       IFNULL(SUM(s.views), 0)    AS num_views, 
       IFNULL(AVG(s.ratings), 0)  AS avg_rating 
FROM   ps 
       LEFT OUTER JOIN tblstatistics s 
         ON s.presiid = ps.id 
WHERE  ps.active = 1 
GROUP BY ps.id, pd.title

In any case, this way you are aggregating less columns, so it can make a difference on the execution time anyway.

Answer 3

You may need to completely refactor the query to be something like this:

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
    (
        select id,title from ps where active=1
        order by datepublished desc
        LIMIT 0,12
    ) 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 
        where presiid in
        (
            select id from ps where active=1
            order by datepublished desc
        )
        group by presiid 
    ) s 
    on ps.id = s.presiid
;

The main idea is shorten the list of keys by gathering only 12 keys before aggregating anything. It is possible to do this because I have answered a similar question on StackOverflow. I tested this with wads of sample data.

Please run this query.

If it does not work, please let me know.

You will also need a decent index

ALTER TABLE ps ADD INDEX (active,datepublished,id);

UPDATE 2012-02-03 08:45 EDT

Try collecting the 12 keys first in a separate table then JOIN

create table pskeys_title
    select id,title from ps where active=1
    order by datepublished desc
    LIMIT 0,12;
alter table pskeys_title add primary key (id);
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
    pskeys_title 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 
        where presiid in
        (
            select id from pskeys_title
        )
        group by presiid 
    ) s 
    on ps.id = s.presiid
;

Answer 4

Since you only want to aggregate for a tiny number (only 12) of the presiid values, use the mantra LIMIT first, JOIN later:

SELECT 
    ps.id, ps.title,
    COALESCE(SUM(s.likes), 0)    AS num_likes,
    COALESCE(SUM(s.comments), 0) AS num_comments,
    COALESCE(SUM(s.ratings), 0)  AS num_ratings,
    COALESCE(SUM(s.views), 0)    AS num_views,
    COALESCE(AVG(s.ratings), 0)  AS avg_rating
FROM  
    ( SELECT datepublished, id, title 
      FROM ps
      WHERE active = 1
      ORDER BY datepublished DESC
        LIMIT 12 OFFSET 0  
    ) AS ps
  LEFT OUTER JOIN
    tblstatistics AS s
      ON s.presiid = ps.id
GROUP BY 
    ps.id 
ORDER BY 
    ps.datepublished DESC;

An index on (active, datepublished, id) will help running the subquery and identifying the 12 needed rows fast. I suppose you already have an index on (presiid).