Where to store average value in db?

Question

I have a restaurant review app which stores reviews in a db, MySql.
There's 2 tables. 1 for Restaurant. Another for reviews.

To increase the performance, I am considering to store the avg review of restaurant instead of calculating it on the fly.

I am not sure where to store that bit though. Should it go into Restaurant table or separate table?

I was thinking if I should create a new table called RestaurantStats to store more than just avg reviews, but is that overkill?

Is it safe to store the avg in main table?

Currently, I am getting around a thousand reviews per day now and expect it to swell in a few months.

I have to show avg of reviews for a list of restaurants by area.
As part of performance tuning, I have created a new table that notes which restaurants were reviewed and then a event is scheduled to run every x minutes to calculate the avg for restaurants that were recently reviewed so that I dont have to calculate avg on every request.

I have my db hosted on AWS, and want to keep the CPU usage down.

Answer 1

Updated answer
There are benefits and drawbacks to adding a new table, and to storing it on the Restaurant table.

Benefits to putting it directly on the restaurant table is that you get all your Restaurant info in a single read. However if this is a large row with lots of data you may not want to update it all the time.

Putting it on a secondary "stats" table also has merit, in that it's essentially transient data. You loose nothing by dropping the table and regenerating it, and updates are fast and light.

Original Answer before it was clear that caching the average was necessary

To increase performance sounds like premature optimization to me. I'll bet that in this case the performance gain is negligible and not worth the overhead.

Consider the overhead, in your application logic when a review is added, you've to fetch all the other scores, average them, and update the stored average.
You've to isolate this operation (probably using a Db transaction), because you can't have two reviews added at the same time.

You've to do this when reviews are added, removed and updated.

Db transactions are expensive, and they're blocking. If you're that worried about performance this will harm your application far more than an simple aggregation.

If you manually change data, or archive data directly then you've to update ALL the affected averages.

Conversely, your Db engine has been insanely well optimised for doing aggregate operations on the fly.

So to answer the question as asked . . .
I'd only do this if you have measured your application performance and you know you absolutely do need it. In that case, store it on the Restaurant table.

Otherwise don't store the value, calculate it on the fly.

Answer 2

You can store this whereever you like. I would store it in the Restaurant table but another one for stats might make sense - it depends how often you update it, and whether you update the restaurant entries often or at all.

An alternative approach would be to store the average in the business logic that fetches and returns the data. The average is then calculated on startup of your services and is dynamically updated (ie cached). You would not have to update the average in the DB then - which is often more expensive than reading a lot of data (ie if you update the average you've turned a read of rows in your table into a single write. If you write this every time a review is added, you're going to be making a lot of relatively expensive update calls)

Answer 3

I would not store it at all.

What you want to use here is a "VIEW". Not a new table. The view is the result of a query, kind of a virtual table.

Last: think hard about your architecture, your solution might not scale well on an RDBMS

Answer 4

Your problem has a solution on other RDBMS (postgresql/oracle) without creating any table : a materialized view

Update it with your scheduled task with refresh materialized view

Since you got MySQL you can only simulate a materialized view via a temporary table. :

• http://www.materialized.info/ or
• https://dev.mysql.com/doc/refman/5.0/en/create-view.html (search for Rafael Palacios comment)