4

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.

12
  • 9
    You have a performance issue with getting an average? How many reviews do you have?
    – paparazzo
    Aug 5, 2015 at 10:15
  • 7
    Show your database schema, e.g. the CREATE TABLE and CREATE INDEX SQL statements. Give an approximation of the number of rows (thousands, millions, billions). What make you think that the average is expensive to compute for your DBMS? So edit your question to improve it. Aug 5, 2015 at 10:30
  • 2
    I guess you are wrong in thinking that computing the average is costly. If you absolutely want to keep something in the database, it should be the cumulated sum of notes, not the average. Aug 5, 2015 at 10:42
  • 2
    It certainly can be quite slow. I had a similar situation on a website, where the top5 entries where listed on all pages. This means that all rows on the reviews table (250k) had to be read to get the averages for all restaurants and then order by that. This made the performance really bad.
    – Flo
    Aug 5, 2015 at 10:47
  • 1
    No it is not simpler to have computed column running on a schedule. A query to sort on an aggregate column is simple. Create a view is simple. This sure sounds like premature optimization to me.
    – paparazzo
    Aug 5, 2015 at 11:18

4 Answers 4

2

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.

5
  • As mentioned in my (updated) question, I am calculating average via mysql events. So the transaction overheads you are talking about dont exist.
    – Alexander
    Aug 5, 2015 at 10:54
  • 4
    It is not necessary to fetch previous scores to calculate the new average when a review is added. If the number of old reviews is n and the old average is a, and the added score is s, then the new average will be (a * n + s) / (n + 1).
    – COME FROM
    Aug 5, 2015 at 11:03
  • 2
    @COMEFROM: I was thinking that there could be a formula for extending an average with another value, but given that that the division happens on smaller numbers, multiple times, isn't it possible that rounding errors could become significant? Wouldn't you need to periodically clean the data by recalculating the averages from scratch? Aug 5, 2015 at 11:06
  • 2
    @BinaryWorrier Yes, rounding errors would be significant. I think it would be wiser to store the sum of the scores and use that to calculate the average.
    – COME FROM
    Aug 5, 2015 at 11:09
  • Or you could store the average and recalculate from scratch periodically (probably daily) to eliminate rounding errors.
    – Jules
    Aug 5, 2015 at 14:24
1

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)

4
  • Well, I will be calling update from db, via trigger or event. And yes, as you said updating it for every update is really bad idea. So I am doing as I explained in my question.
    – Alexander
    Aug 5, 2015 at 10:51
  • Well, updating it (say) daily is sensible, but you're really just using the DB as an expensive, but convenient, cache for this value. I recommend storing this cached value in a cheaper cache is all.
    – gbjbaanb
    Aug 5, 2015 at 10:57
  • That would makes sense if I have a small list of values to cache, but initiating a cache of 10k vaues is overkill, isn't it? Especially if I know over 70% of that wont be used at all.
    – Alexander
    Aug 5, 2015 at 11:02
  • You will be doing this anyway as reviews are received. With a temporary cache, you don't have to cache everything, only when required. So when read, if the value is a default uninitialised value (eg -1 or 0) then it means the cache needs to be populated and you calculate it there and then. So your cache grows with populated values dynamically. This is a simple and efficient pattern to follow.
    – gbjbaanb
    Aug 5, 2015 at 11:08
0

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

3
  • 3
    I don't see how a view would improve performance in this instance.
    – Jules
    Aug 5, 2015 at 14:26
  • @Jules You should learn about materialized views then, which exists in major RDBMS like oracle,postgres...
    – NeeL
    Aug 5, 2015 at 21:09
  • @NeeL but not in mysql, which the OP explicitly states is what he is using.
    – Jules
    Aug 5, 2015 at 22:46
0

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. :

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.