How to speed up mysql query using cache

Question

I'm using the following query to create a leaderboard of top 10 people in this week.

SELECT 
users.id, 
concat(users.first_name, ' ', users.last_name) as name, 
users.picture_url, 
MAX(rounds.speed) as speed, 
AVG(rounds.accuracy) as accuracy, 
SUM(rounds.score) as score, 
users.level 
FROM users INNER JOIN rounds ON users.email=rounds.email 
WHERE DATE(rounds.dateTime) BETWEEN CURDATE()-INTERVAL 1 WEEK AND CURDATE() 
GROUP BY users.id  
ORDER BY score DESC, speed DESC 
LIMIT 10

Currently, it takes around 6 seconds to run this query. The table 'rounds' contain around 3000 rows. Soon it will be much bigger. So once the user opens the leaderboard it takes more than 6 seconds to load!

Is there any way of caching or improving the query so that it loads faster?

Database: MySQL Backend: PHP5 Framework: Codeigniter

But there can be other ways instead of just improving query. Like caching in CodeIgniter or something. That's why I posted here — Gijo Varghese
– Gijo Varghese, Commented Dec 26, 2016 at 5:00

Community · Accepted Answer · 2020-06-20 09:12:55Z

There are a number of things you can do to address this - the query is slow due to the MAX and AVG elements which involve comparative analysis among all of the rows in your table.

There are a couple of things that you could do to make this faster.

Break it into multiple queries. You're grabbing the top 10 users based on ORDER BY score DESC, speed DESC, so you don't need to also get the AVG(rounds.accuracy) in the same query. Get user_id of the top 10 users and then query the database separately for the AVG(rounds.accuracy) of those 10 users.
Add some logic based on the nature of your leaderboard. It's unlikely that you need to check all of the rows every time that you update the leaderboard. Someone who is 100th on the leaderboard might be unlikely to move into the top 10 today, and if that's the case you wouldn't need to check that person's score today.

It will speed up the query significantly if you add a WHERE user_id IN statement, and include only the top number of users who are likely to be in the top 10. Maybe on any given day there are only 30, 40 or 50 users who are likely to be in the top 10. If that's the case then you could create a separate table containing the top 50 user ids, run a "slower" query once per day to get those top 50. In your leaderboard query add a WHERE user_id IN (select id from top_users) condition.

There are likely other approaches and ways to improve your query speed. These are a couple things that will help. A lot of this depends on your app/leaderboard and also what you learn about it as you see it being used.

Gaurav Agrawal · Accepted Answer · 2016-12-26 06:15:18Z

1

Adding @Dan answer . Use Explain to check why query is Taking time , Check if indexes are properly build and as you want data only last week , you can create index on datetime which would be very helpful .As you are using math functions and order by , Mysql need to sort and calculate the values which increase the time of the Query. Also you can enable mysql cache and increase the Cache size check this Here .

edited Dec 26, 2016 at 6:15

answered Dec 26, 2016 at 6:08

Gaurav Agrawal

951 gold badge11 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to speed up mysql query using cache

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related