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?
假设我有一个狗狗桌子,t_dogs,包含所有者名称,品种,体重,年龄等等。如果我知道某些计数查询会经常执行,例如某个所有者的狗总数或年龄,我应该使用COUNT(*)FROM ... WHERE ...还是应该创建一个单独的表来跟踪所有者,年龄等的狗总数?
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!
1 个解决方案
#1
2
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.
对于MySQL,您使用的存储引擎很重要。 InnoDB使用COUNT(*)查询IIRC表现不佳。对于MyISAM而言,这并不重要。
#1
2
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.
对于MySQL,您使用的存储引擎很重要。 InnoDB使用COUNT(*)查询IIRC表现不佳。对于MyISAM而言,这并不重要。