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 a query that is taking a particularly long time to run (15+ seconds) and it is only getting worse with time as my dataset grows. I have optimized this in the past, and have added indices, code-level sorting and other optimizations, but it needs some further refining.

SELECT sounds.*, avg(ratings.rating) AS avg_rating, count(ratings.rating) AS votes FROM `sounds` 
INNER JOIN ratings ON sounds.id = ratings.rateable_id 
WHERE (ratings.rateable_type = 'Sound' 
   AND sounds.blacklisted = false 
   AND sounds.ready_for_deployment = true 
   AND sounds.deployed = true 
   AND sounds.type = "Sound" 
   AND sounds.created_at > "2011-03-26 21:25:49") 
GROUP BY ratings.rateable_id

The query's purpose is to get me the sound id's and the average rating of the most recent, released sounds. There are about 1500 sounds, and 2 Million ratings.

I have several indices on sounds

mysql> show index from sounds;
+--------+------------+------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+————+
| Table  | Non_unique | Key_name                                 | Seq_in_index | Column_name          | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+————+
| sounds |          0 | PRIMARY                                  |            1 | id                   | A         |        1388 |     NULL | NULL   |      | BTREE      |         | 
| sounds |          1 | sounds_ready_for_deployment_and_deployed |            1 | deployed             | A         |           5 |     NULL | NULL   | YES  | BTREE      |         | 
| sounds |          1 | sounds_ready_for_deployment_and_deployed |            2 | ready_for_deployment | A         |          12 |     NULL | NULL   | YES  | BTREE      |         | 
| sounds |          1 | sounds_name                              |            1 | name                 | A         |        1388 |     NULL | NULL   |      | BTREE      |         | 
| sounds |          1 | sounds_description                       |            1 | description          | A         |        1388 |      128 | NULL   | YES  | BTREE      |         | 
+--------+------------+------------------------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+

and several on ratings

mysql> show index from ratings;
+---------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+————+
| Table   | Non_unique | Key_name                                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+————+
| ratings |          0 | PRIMARY                                 |            1 | id          | A         |     2008251 |     NULL | NULL   |      | BTREE      |         | 
| ratings |          1 | index_ratings_on_rateable_id_and_rating |            1 | rateable_id | A         |          18 |     NULL | NULL   |      | BTREE      |         | 
| ratings |          1 | index_ratings_on_rateable_id_and_rating |            2 | rating      | A         |        9297 |     NULL | NULL   | YES  | BTREE      |         | 
+---------+------------+-----------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

Here is the EXPLAIN

mysql> EXPLAIN SELECT sounds.*, avg(ratings.rating) AS avg_rating, count(ratings.rating) AS votes FROM sounds INNER JOIN ratings ON sounds.id = ratings.rateable_id WHERE (ratings.rateable_type = 'Sound' AND sounds.blacklisted = false AND sounds.ready_for_deployment = true AND sounds.deployed = true AND sounds.type = "Sound" AND sounds.created_at > "2011-03-26 21:25:49") GROUP BY ratings.rateable_id;
+----+-------------+---------+--------+--------------------------------------------------+-----------------------------------------+---------+-----------------------------------------+---------+——————+
| id | select_type | table   | type   | possible_keys                                    | key                                     | key_len | ref                                     | rows    | Extra       |
+----+-------------+---------+--------+--------------------------------------------------+-----------------------------------------+---------+-----------------------------------------+---------+——————+
|  1 | SIMPLE      | ratings | index  | index_ratings_on_rateable_id_and_rating          | index_ratings_on_rateable_id_and_rating | 9       | NULL                                    | 2008306 | Using where | 
|  1 | SIMPLE      | sounds  | eq_ref | PRIMARY,sounds_ready_for_deployment_and_deployed | PRIMARY                                 | 4       | redacted_production.ratings.rateable_id |       1 | Using where | 
+----+-------------+---------+--------+--------------------------------------------------+-----------------------------------------+---------+-----------------------------------------+---------+-------------+

I do cache the results once obtained, so site performance is not much of an issue, but my cache warmers are taking longer and longer to run due to this call taking so long, and that is starting to become an issue. This doesn't seem like a lot of numbers to crunch in one query…

What more can I do to make this perform better?

share|improve this question
    
Can you show the EXPLAIN output? EXPLAIN SELECT sounds.*, avg(ratings.rating) AS avg_rating, count(ratings.rating) AS votes FROM sounds INNER JOIN ratings ON sounds.id = ratings.rateable_id WHERE (ratings.rateable_type = 'Sound' AND sounds.blacklisted = false AND sounds.ready_for_deployment = true AND sounds.deployed = true AND sounds.type = "Sound" AND sounds.created_at > "2011-03-26 21:25:49") GROUP BY ratings.rateable_id –  Derek Downey May 21 '11 at 21:47
    
updated. thanks for asking –  coneybeare May 21 '11 at 22:09
    
@coneybeare This was a very interesting challenge for me today !!! +1 for you question. I wish more questions like this come along in the near future. –  RolandoMySQLDBA May 22 '11 at 1:25
    
@coneybeare It looks like the new EXPLAIN only reads 21540 rows (359 X 60) instead of 2,008,306. Please run the EXPLAIN on the query I originally suggested in my answer. I would like to see the number of rows that come from that. –  RolandoMySQLDBA May 22 '11 at 2:45
    
@RolandoMySQLDBA The new explain does indeed show that smaller amount of rows with the index, however, the time to execute the query was still about 15 seconds, showing no improvement –  coneybeare May 22 '11 at 3:22
show 8 more comments

4 Answers

up vote 6 down vote accepted

After looking over the query, the tables, and the WHERE AND GROUP BY clauses, I recommend the following:

Recommendation #1) Refactor the Query

I reorganized the query to do three(3) things:

  1. create smaller temp tables
  2. Process the WHERE clause on those temp tables
  3. Delay joining to the very last

Here is my proposed query:

SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

Recommendation #2) Index the sounds table with an index that will accommodate the WHERE clause

The columns of this index include all the columns from the WHERE clause with static values first and moving target last

ALTER TABLE sounds ADD INDEX support_index
(blacklisted,ready_for_deployment,deployed,type,created_at);

I sincerely believe you will be pleasantly surprised. Give it a Try !!!

UPDATE 2011-05-21 19:04

I just saw the cardinality. OUCH !!! Cardinality of 1 for rateable_id. Boy, I feel stupid !!!

UPDATE 2011-05-21 19:20

Maybe making the index will be enough to improve things.

UPDATE 2011-05-21 22:56

Please run this:

EXPLAIN SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes FROM
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

UPDATE 2011-05-21 23:34

I refactored it again. Try This One Please:

EXPLAIN
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes FROM
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
;

UPDATE 2011-05-21 23:55

I refactored it again. Try This One Please (Last Time):

EXPLAIN
  SELECT A.id,avg(B.rating) AS avg_rating, count(B.rating) AS votes FROM
  (
    SELECT BB.* FROM
    (
      SELECT id FROM sounds
      WHERE blacklisted = false 
      AND   ready_for_deployment = true 
      AND   deployed = true 
      AND   type = "Sound" 
      AND   created_at > '2011-03-26 21:25:49'
    ) AA INNER JOIN sounds BB USING (id)
  ) A INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) B
  ON A.id = B.rateable_id
  GROUP BY B.rateable_id;

UPDATE 2011-05-22 00:12

I hate giving up !!!!

EXPLAIN
  SELECT A.*,avg(B.rating) AS avg_rating, count(B.rating) AS votes FROM
  (
    SELECT BB.* FROM
    (
      SELECT id FROM sounds
      WHERE blacklisted = false 
      AND   ready_for_deployment = true 
      AND   deployed = true 
      AND   type = "Sound" 
      AND   created_at > '2011-03-26 21:25:49'
    ) AA INNER JOIN sounds BB USING (id)
  ) A,
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
    AND AAA.rateable_id = A.id
  ) B
  GROUP BY B.rateable_id;

UPDATE 2011-05-22 07:51

It has been bothering me that ratings is coming back with 2 million rows in the EXPLAIN. Then, it hit me. You might need another index on the ratings table which starts with rateable_type:

ALTER TABLE ratings ADD INDEX
rateable_type_rateable_id_ndx (rateable_type,rateable_id);

The goal of this index is to reduce the temp table that manipulates ratings so that it is less that 2 million. If we can get that temp table significantly smaller (at least half), then we can have a better hope in your query and mine working faster too.

After making that index, please Retry my original proposed query and also try yours:

SELECT
  sounds.*,srkeys.avg_rating,srkeys.votes
FROM
(
  SELECT AA.id,avg(BB.rating) AS avg_rating, count(BB.rating) AS votes
  (
    SELECT id FROM sounds
    WHERE blacklisted = false 
    AND   ready_for_deployment = true 
    AND   deployed = true 
    AND   type = "Sound" 
    AND   created_at > '2011-03-26 21:25:49'
  ) AA INNER JOIN
  (
    SELECT AAA.ratings,AAA.rateable_id
    FROM ratings AAA
    WHERE rateable_type = 'Sound'
  ) BB
  ON AA.id = BB.rateable_id
  GROUP BY BB.rateable_id
) srkeys INNER JOIN sounds USING (id);

UPDATE 2011-05-22 18:39 : FINAL WORDS

I had refactored a query in a stored procedure and added an index to help answer a question on speeding things up. I got 6 upvotes, had the answer accepted,and picked up a 200 bounty.

I had also refactored another query (marginal results) and added an index (dramatic results). I got 2 upvotes and had the answer accepted.

I added an index for yet another query challange and was upvoted once

and now your question.

Wanting to answers all questions like these (including yours) were inspired by a YouTube video I watched on refactoring queries.

Thank you again, @coneybeare !!! I wanted to answer this question to fullest extent possible, not just accept points or accolades. Now, I can feel that I earned the points !!!

share|improve this answer
    
I added the index, no improvement on time. Here is the new EXPLAIN: cloud.coneybeare.net/6y7c –  coneybeare May 22 '11 at 2:03
    
The EXPLAIN on the query from recommendation 1: cloud.coneybeare.net/6xZ2 It took about 30 seconds to run this query –  coneybeare May 22 '11 at 3:19
    
I did have to edit your syntax a little for some reason (I added a FROM before the first query, and I had to get rid of AAA alias). Here is the EXPLAIN: cloud.coneybeare.net/6xlq The actual query took about 30 seconds to run –  coneybeare May 22 '11 at 3:40
    
@RolandoMySQLDBA: EXPLAIN on your 23:55 update: cloud.coneybeare.net/6wrN The actual query ran over a minute so i killed the process –  coneybeare May 22 '11 at 4:04
    
The second inner select cannot access the A select table, thus A.id throws error. –  coneybeare May 22 '11 at 4:57
show 2 more comments

Thanks for the EXPLAIN output. As you can tell from that statement, the reason it's taking so long is the full tablescan on the ratings table. Nothing in the WHERE statement is filtering down the 2million rows.

You could add an index on ratings.type, but my guess is the CARDINALITY is going to be real low and you'll still be scanning quite a few rows on ratings.

Alternatively you can try to use index hints to force mysql to use the sounds indexes.

Updated:

If it were me, I'd add an index on sounds.created as that has the best chance of filtering the rows and will probably force the mysql query optimizer to use the sounds table indexes. Just beware of queries that use long created time frames (1 year, 3 months, just depends on the size of the sounds table).

share|improve this answer
    
Looks like your suggestion was notable for @coneybeare. +1 from me as well. –  RolandoMySQLDBA May 22 '11 at 1:15
    
The index on created didnt shave off any time. Here is the updated EXPLAIN. cloud.coneybeare.net/6xvc –  coneybeare May 22 '11 at 1:58
add comment

If this has to be an "on-the-fly" available query, then that limits your options a bit.

I'm going to suggest divide and conquer for this problem.

--
-- Create an in-memory table
CREATE TEMPORARY TABLE rating_aggregates (
rateable_id INT,
avg_rating NUMERIC,
votes NUMERIC
);
--
-- For now, just aggregate. 
INSERT INTO rating_aggregates
SELECT ratings.rateable_id, 
avg(ratings.rating) AS avg_rating, 
count(ratings.rating) AS votes FROM `sounds`  
WHERE ratings.rateable_type = 'Sound' 
GROUP BY ratings.rateable_id;
--
-- Now get your final product --
SELECT 
sounds.*, 
rating_aggregates.avg_rating, 
rating_aggregates.votes AS votes,
rating_aggregates.rateable_id 
FROM rating_aggregates 
INNER JOIN sounds ON (sounds.id = rating_aggregates.rateable_id) 
WHERE 
ratings.rateable_type = 'Sound' 
   AND sounds.blacklisted = false 
   AND sounds.ready_for_deployment = true 
   AND sounds.deployed = true 
   AND sounds.type = "Sound" 
   AND sounds.created_at > "2011-03-26 21:25:49";
share|improve this answer
    
seems @coneybeare saw something in your suggestion. +1 from me !!! –  RolandoMySQLDBA May 22 '11 at 1:16
    
I actually couldn't get this to work. I was getting sql errors that I was unsure of how to approach. I have never really worked with temporary tables –  coneybeare May 22 '11 at 2:39
    
I did get it eventually ( I had to add FROM sounds, ratings to the middle query), but it locked up my sql box and I had to kill the process. –  coneybeare May 22 '11 at 2:49
add comment

Use JOINs, not subqueries. Did any of your subquery attempt help?

SHOW CREATE TABLE sounds \G

SHOW CREATE TABLE ratings \G

Often it is beneficial to have "compound" indexes, not single-column ones. Perhaps INDEX(type, created_at)

You are filtering on both tables in a JOIN; that is likely to be a performance problem.

There are about 1500 sounds, and 2 Million ratings.

Recommend you have an auto_increment id on ratings, build a summary table, and use the AI id for keeping track of where you "left off". However, do not store averages in a summary table:

avg(ratings.rating) AS avg_rating,

Instead, keep the SUM(ratings.rating). The average of averages is mathematically incorrect for computing an average; (sum of sums) / (sum of counts) is correct.

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.