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.

After looking at my mysql slow log file I see that almost every entry there is related to this query:

SELECT 
    n.nid AS nid, AVG(v.value) AS rating
FROM 
  node n
  LEFT OUTER JOIN votingapi_vote v 
    ON  v.entity_id = n.nid 
    AND v.timestamp < UNIX_TIMESTAMP()-304800 
    AND v.timestamp > UNIX_TIMESTAMP()-1524000
WHERE 
    ( (n.type = 'gallery') AND (n.status = '1') )
GROUP BY 
    n.nid
ORDER BY 
    rating DESC, n.created DESC
LIMIT 20 OFFSET 0;

Slow log shows this stats: Query_time: 1 Lock_time: 0 Rows_sent: 20 Rows_examined: 1981034

As you may see it joins 2 tables - node (45000 rows) and votingapi_vote (only 705 rows for now). votingapi_vote will grow in future and will have thousands rows. Node table has a content rows like n.nid, n.title, n.created, n.type, n.status. Votingapi_vote table has a voting rows like v.entity_id, v.value, v.timestamp

So I need to get list of 20 nodes with type gallery who has the best average rating for selected period of time.

Can you pls help me to optimize this query?

Thanks!

UPDATE: This is EXPLAIN results:

+----+-------------+-------+------+----------------------------+------------------+---------+-------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys              | key              | key_len | ref         | rows | Extra                                        |
+----+-------------+-------+------+----------------------------+------------------+---------+-------------+------+----------------------------------------------+
|  1 | SIMPLE      | n     | ref  | node_status_type,node_type | node_status_type | 102     | const,const |  830 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | v     | ALL  | NULL                       | NULL             | NULL    | NULL        |  759 |                                              | 
+----+-------------+-------+------+----------------------------+------------------+---------+-------------+------+----------------------------------------------+
2 rows in set (0.00 sec)

UPDATE 2: This is EXPLAIN for query where I changed LEFT OUTER JOIN to INNER JOIN:

mysql> EXPLAIN SELECT      n.nid AS nid, AVG(v.value) AS rating FROM    node n   INNER JOIN votingapi_vote v      ON  v.entity_id = n.nid      AND v.timestamp < UNIX_TIMESTAMP()-304800      AND v.timestamp > UNIX_TIMESTAMP()-1524000 WHERE      ( (n.type = 'gallery') AND (n.status = '1') ) GROUP BY      n.nid ORDER BY      rating DESC, n.created DESC LIMIT 20 OFFSET 0;
+----+-------------+-------+--------+------------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                      | key     | key_len | ref                         | rows | Extra                                        |
+----+-------------+-------+--------+------------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | v     | ALL    | NULL                               | NULL    | NULL    | NULL                        |  653 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | n     | eq_ref | PRIMARY,node_status_type,node_type | PRIMARY | 4       | drupal.v.entity_id |    1 | Using where                                  | 
+----+-------------+-------+--------+------------------------------------+---------+---------+-----------------------------+------+----------------------------------------------+
2 rows in set (0.01 sec)

Is INNER JOIN better? Btw it returned 0 results (without EXPLAIN). So looks like INNER JOIN wrong in this case.

share|improve this question
    
Why the LEFT - and not INNER join? –  ypercube Dec 31 '13 at 17:37
    
Maybe index on type,status if are not there already.Also add EXPLAIN before SELECT and show the results. –  Mihai Dec 31 '13 at 17:42
    
Shall we guess that this is MySQL? –  ypercube Dec 31 '13 at 18:01
    
Yes this is MySQL. –  Dmitry Dec 31 '13 at 18:35
    
Is INNER join better in this case? –  Dmitry Dec 31 '13 at 18:40

1 Answer 1

It's tough to optimise Aggregation (AVG), Group By and Order by. These operations even if well indexed will generally cost internal temp tables and regularly on-disk temp tables for grouping and sorting. It's widely accepted to cache the results for re-use rather then rerunning this at any level of concurrency. You may have heard of summary tables to record such values. Worth a read, google is your friend.

share|improve this answer

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.