which one is better to calculate star rating average (in performance sense)

Question

i thinking which one is the best way to show average star rating. is it better to calculate the average when there's a review with star value given, and store the average in DB field, so when i load a page i just check 1 field's value? or calculate the average each time user loading the page?

Answer 1

Without a sample schema, and an idea of typical usage, it's almost impossible to provide a good answer.

The question you pose is "should I denormalize my database" - there are lots of other questions on this topic.

From a performance point of view, the question boils down to "how often do you have to write, how often do you have to read, and how important is it that data is consistent?".

If your application user experience is such that "star ratings" are shown almost never, and calculating that star rating is "cheap", then the performance impact is low.

If you are showing long, scrolling pages with items, each with a star rating, the performance benefit could be high, especially if calculating the star rating is an expensive operation.

If it's important that star ratings are exactly accurate in all cases, you will have to add some additional logic like locking behaviour which could have a huge impact on your database.

If your application experience means that you may have periods with very high numbers of new ratings, you could have a significant performance impact on the "write" operation.

In general, it's best to design your application to be normalized (so it's easy to debug and maintain), and to measure whether you need to do anything more. Modern database engines can handle far more than most people realize.

** update **

Thanks for your update.

Your schema suggestion should be lightning fast without denormalization - you should be joining on a foreign key on the reviews table. It all depends on the exact circumstances, but unless you need to scale to hundreds of millions of products and reviews, I doubt you'd ever see a measurable difference in database performance. The logic to keep the "average score" column updated may be more of a performance overhead than calculating it on the fly.

In my experience, denormalization is an expensive thing to do - it makes your code much harder to understand and debug, and leads to entertaining bugs. From a performance point of view, if you're building a website, you'll get a much better return by focusing on caching at the HTTP level.