Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

4 Answers 4

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.

share|improve this answer
1  
I did not run the EXPLAIN on production DB which is why you see 2643 and not 100,000 + and there are a lot other WHERE conditions which I excluded from my question. –  Jimmy Feb 2 '12 at 16:32
    
Ahh! Missing information affects the answer. Can you show the indexes for the appropriate tables and include those columns in the WHERE condition that you think should be using said indexes? –  Derek Downey Feb 2 '12 at 16:35
    
Those tables and extra fields are using INDEXES correctly, however, my question was could this subquery be written in any better way than above because looking at the EXPLAIN, this is a full table scan even though I have some indexes on tblstatistics defined. –  Jimmy Feb 2 '12 at 16:40
    
In other words, are there any better alternatives for derived tables? –  Jimmy Feb 2 '12 at 16:42
    
Yes, I think @mysqlRolando is on the right track with limiting your available aggregate to only the 12 presiid's you are interested in. –  Derek Downey Feb 2 '12 at 17:35
add comment

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.

share|improve this answer
add comment

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
;
share|improve this answer
    
Interesting....however when I run, I get this - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' and I am on MySQL 5.5.10 –  Jimmy Feb 2 '12 at 19:15
    
In the other answer I gave the link for, I was using MySQL 5.5.12 from my Windows Desktop. –  RolandoMySQLDBA Feb 2 '12 at 20:08
    
Unfortunately, I am not in a position to upgrade MySQL just yet... –  Jimmy Feb 3 '12 at 9:00
    
Not sure what's going on here....I upgraded my local MYSQL Server version to 5.5.19 and I still get the same error when I run the above query....not sure how you got it running? I have read that MYSQL does not support LIMIT in subqueries at all....dev.mysql.com/doc/refman/5.5/en/subquery-restrictions.html –  Jimmy Feb 3 '12 at 11:51
    
@Jimmy Now I understand. I think I see the problem. The second LIMIT clause is not allowed because LIMIT is not allowed in the subquery WHERE IN from your very first comment. I updated the query in my answer. Try it now. –  RolandoMySQLDBA Feb 3 '12 at 13:40
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.