CODEWITHSUNDEEP
mysqlperformance
Rmilligan2372
Rmilligan2372

Reputation: 117

MySQL Performance Issue using Select, Count, and Group By

I've been researching the best way to write this query, but I can't seem to get the performance to improve.

The query is used to build a "trending topics" tagcloud.

There are about a million records in the table and growing.

The InnoDB table structure is...

id - int(15) [AI, Primary, Unique]
topic - varchar(50)
datetime

And here is my query...

SELECT topic, COUNT(topic) AS topicCount 
FROM table 
WHERE datetime > DATE_SUB( NOW(), INTERVAL 24 HOUR) AND topic!='' 
GROUP BY topic HAVING topicCount>1 
ORDER BY topicCount DESC 
LIMIT 10

The performance of the query is hovering around 300ms which is far too slow.

I added an index to topic, but the performance jumped to 1400ms.

Is there a more efficient way to write this query that would greatly improve performance?

Upvotes: 0

Views: 55

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You have done indexing on topic but that will not improve the performance.

Add an index to datetime since you are doing range search and having index on that column will improve the performance.

Upvotes: 1

Related Questions