a design about cache(using redis) , and some problem about it

[Xin Liu]

Now, Let's look at the following scenario:

A website manage 10,000 students, and show top 100 of student's achievement.

I use mysql storage every student personal information(name email sex and achievement)

but I have to get every student achievement and caculate Top 100, so I use redis to solve this problem, cache the Top 100 with redis and storage it in sorted set data type.

But now, the problem is: if one student achievement is change, should I update the redis‘s Top 100 every time？？

this case may be good， you can think it as a game score Top 100,

Thanks for advance, : )

[Michiel Overtoom]

> On 2016-04-10, at 14:04, Xin Liu <username...@gmail.com> wrote:
>
> show top 100 of student's achievement

MySQL query to get the 100 students with best achievements:

select student_id, student_name, student_score
from Students
order by student_score desc
limit 100

Greetings,

[Brendan Quinn]

This looks like a case where relying on some properties of your problem domain can help.

One thing you could do is to store the score/grade of the lowest-ranking student who is still in the top 100, ie the "threshold" score that puts you in to the top 100. You could store the "threshold" value in your redis cache as well.

Then each time a student's score changes, you can check whether

  (a) the student's previous score was above the threshold and their score has decreased to be below the threshold (ie they've dropped out of the top 100),

  or  (b) if the student's previous score was below the threshold and is now above it (ie they've just entered the top 100).

If either of those is true, you will need to recalculate and cache the top 100 list. But if neither condition is true (which should be most of the time), you can leave the cache untouched.

There are probably other smart things you can do so that you don't have to re-execute a MySQL query each time you rebuild the top 100 list, but this optimisation should save you a lot of queries.

Hope that helps,

Brendan.