In what way does denormalization improve database performance?

by

Denormalization is generally used to either:

  • Avoid a certain number of queries
  • Remove some joins

The basic idea of denormalization is that you’ll add redundant data, or group some, to be able to get those data more easily — at a smaller cost; which is better for performances.

A quick examples?

  • Consider a “Posts” and a “Comments” table, for a blog
    • For each Post, you’ll have several lines in the “Comment” table
    • This means that to display a list of posts with the associated number of comments, you’ll have to:
      • Do one query to list the posts
      • Do one query per post to count how many comments it has (Yes, those can be merged into only one, to get the number for all posts at once)
      • Which means several queries.
  • Now, if you add a “number of comments” field into the Posts table:
    • You only need one query to list the posts
    • And no need to query the Comments table: the number of comments are already de-normalized to the Posts table.
    • And only one query that returns one more field is better than more queries.

Now, there are some costs, yes:

  • First, this costs some place on both disk and in memory, as you have some redundant informations:
    • The number of comments are stored in the Posts table
    • And you can also find those number counting on the Comments table
  • Second, each time someone adds/removes a comment, you have to:
    • Save/delete the comment, of course
    • But also, update the corresponding number in the Posts table.
    • But, if your blog has a lot more people reading than writing comments, this is probably not so bad.

More Related Contents:

  1. When and why are database joins expensive?
  2. What is the optimal length for an email address in a database?
  3. In terms of databases, is “Normalize for correctness, denormalize for performance” a right mantra?
  4. Minimum no of tables that exists after decomposing relation R into 1NF?
  5. Is storing a delimited list in a database column really that bad?
  6. Should each and every table have a primary key?
  7. Database development mistakes made by application developers [closed]
  8. Keep PostgreSQL from sometimes choosing a bad query plan
  9. Database, Table and Column Naming Conventions? [closed]
  10. Surrogate vs. natural/business keys [closed]
  11. How are super- and subtype relationships in ER diagrams represented as tables?
  12. What is the best design for a database table that can be owned by two different resources, and therefore needs two different foreign keys? [closed]
  13. Normalization: What does “repeating groups” mean?
  14. First name, middle name, last name. Why not Full Name?
  15. Relational table naming convention [closed]
  16. What are the design criteria for primary keys?
  17. When I should use one to one relationship?
  18. Supertype-subtype database design
  19. Database optimization orders
  20. What’s wrong with nullable columns in composite primary keys?
  21. Best representation of an ordered list in a database?
  22. have address columns in each table or an address table that is referenced by the other tables?
  23. Limit an sqlite Table’s Maximum Number of Rows
  24. NULL permitted in Primary Key – why and in which DBMS?
  25. What are some best practices and “rules of thumb” for creating database indexes?
  26. When we need to use 1-to-1 relationship in database design?
  27. What’s the better database design: more tables or more columns?
  28. Derived account balance vs stored account balance for a simple bank account?
  29. In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
  30. uncertainty in developing a database model

Leave a Comment