CODEWITHSUNDEEP
phpmysqlsqlperformancepdo
Sakuya
Sakuya

Reputation: 680

Should I use a column to count rows or count them in PHP?

Which is best practice?

Scenario: If I have 2 tables, one called topics and another called posts whereby each post belongs to a specific topic_id.

Would it be best to have a column in the topics table called post_count which I would update whenever a post is created/delete under that topic_id.

Or would it be best to count the number of rows where a topic_id matches using a SELECT COUNT query?

Are there any disadvantages to either or differences in efficiency?

Upvotes: 2

Views: 66

Answers (1)

David John Smith
David John Smith

Reputation: 1864

Storing post_count on column is a denormalisation.

The read performance will be faster to look up a single field than to perform a SELECT COUNT, but most people would agree that this is a premature optimisation that introduces the possiblity of update anomaly. The database should store normalised (i.e. non-redundant) data unless you have benchmarks indicating that this is the performance bottleneck in your application.

You would probably be better served by looking at a caching layer between the database and the application so that the count is not performed each time you query the database, but will be updated when the contents of posts changes - you may even find that MySQL is already caching the result.

Upvotes: 4

Related Questions