CODEWITHSUNDEEP
mysqldatabase
Luke
Luke

Reputation: 1053

COUNT(*) or separate table for keeping track of total?

Suppose I have a MySQL table of dogs, t_dogs, with columns of owner name, breed, weight, age, etc. If I knew there are certain count queries that'll be performed frequently such as total number of dogs by an certain owner or age, should I use COUNT(*) FROM ... WHERE ... or should I create a separate table for keeping track of total number of dogs by owner, age, etc?

The number of updates to total number of dogs for an owner, age is minimal compared to how many times the total number is queried.

Thanks!

Upvotes: 2

Views: 617

Answers (1)

Hammerite
Hammerite

Reputation: 22340

Creating a separate table is denormalised design, but it might be justified if not doing it would significantly impact performance. If you do choose to do this, then it becomes your job to keep the table of metadata updated properly, which is an additional burden and set of things that can go wrong.

For MySQL it matters what storage engine you are using. InnoDB performs poorly with COUNT(*) queries IIRC. For MyISAM it does not matter so much.

Upvotes: 2

Related Questions