How should I resolve this slow voting query in mysql-slow.log?

Question

I'm currently using django-voting in my website. But it turns out, when number of voting.Vote.objects.counts() comes to 10, 000, this query is slow (1.03 seconds) and appears in mysql-slow.log

# Query_time: 1.031839  Lock_time: 0.000069 Rows_sent: 1  Rows_examined: 72754                                                                            
SET timestamp=1363621528;                                                                                                                                 
SELECT (COALESCE(SUM(vote), 0)) AS `score`, (COALESCE(COUNT(vote), 0)) AS `num_votes`   FROM `votes` WHERE (`votes`.`object_id` = 10136  AND `votes`.`content_type_id` = 48 ) LIMIT 1;

I'm wondering if there is a better way to achieve this?

score 1 · Answer 1 · answered Mar 18 '13 at 16:32

You should take a look on that table votes through mysql client, and execute explain select:

EXPLAIN SELECT (COALESCE(SUM(vote), 0)) AS `score`, (COALESCE(COUNT(vote), 0)) AS `num_votes`   FROM `votes` WHERE (`votes`.`object_id` = 10136  AND `votes`.`content_type_id` = 48 ) LIMIT 1;

then check if columns 'vote' is indexed during this query, if not - you should add new index on 'vote' column.

How should I resolve this slow voting query in mysql-slow.log?

1 Answers1