A Denormalized Counter Column Trades Write Cost for Read Speed
Instead of counting a customer's orders every time a page loads, you can keep an order_count column on the customer row and update it when orders change, by trigger or in application code. Reads become a single column fetch; writes get more expensive, the column can drift out of agreement with the truth, and you need a way to recompute it. Do this only when the count is read far more often than it changes, and always keep the query that recomputes it from scratch so you can check and repair.
Questions this Concept answers
- Why does keeping such a column make writes more expensive?
Denormalisation in Databases â Trigger Drift Pitfalls
This page explains denormalisation: deliberately copying or pre-computing data so a read does not have to join many tables. One of its techniques is keeping a derived column, like a saved order total…