CODEWITHSUNDEEP
mysqldatabasecodeigniterdatabase-designrelational-database
yeh
yeh

Reputation: 51

Mysql : Best way to deal with data like average rating of restaurants etc?

I have a MySQL table which stores restaurant ratings like:

id | user_id | res_id | rating_value | review_id |
 1 |    102  |   5567 |          4.0 |        26 |
 2 |    106  |   5543 |          3.5 |        27 |
 3 |    112  |   5567 |          3.0 |        31 |

and I have Restaurant Profile webpage for each restaurant which shows 'Data' like 'average rating' of the restaurant by users.

Users can Review & Rate the restaurant per/day with a limited number of times, so a single restaurant may receive many new rating rows per/day just form one user alone.

My question is:

  1. Should I run a cronjob daily ( or weekly?) to SELECT AVG(rating_value) of each restaurant to update the 'Rating' of the restaurant, will this consume alot of memory?

  2. Should I just keep like X number of recent 'ratings' and use cronjob to SELECT AVG(rating_value) it daily for each restaurant?

3.Or I should only run the SELECT AVG(rating_value) when a new 'Rating' is submitted?

Upvotes: 0

Views: 1134

Answers (2)

Manmohan
Manmohan

Reputation: 740

There are many ways to handle this kind of queries. Like some :-

You can run SELECT AVG(rating_value) on add new rating (make the column indexed and unique to fetch result quickly)

Second :-

You can run SELECT AVG(rating_value) on add new rating and save it in cache by restaurant id or any other way.

Third :-

On add new rating , you can run that query by cronjob on hourly basis and save it in cache, so the load to database will be reduced.

Thanks

Upvotes: 0

et071385
et071385

Reputation: 103

It sounds like you want to keep the load on your database light for a value that doesn't change unless a new rating is submitted for that restaurant.

I would suggest caching this information in something like memcache or redis since duplicating this average in your database is redundant. You can then set an expiry on this (say one hour) and only go to the DB for the value if it's not in your caching solution.

If you want a real time solution then you should hook into your review submit logic to refresh the cached average value for the restaurant. This will guarantee that your application always displays the most accurate average rating.

If you want to store the data into the database, then I would recommend either a DB trigger to update a table storing this average rating field, or like with the caching solution, have submission of a review hook into updating this value.

Upvotes: 0

Related Questions