blocks|key|1176779|text|如果你记录一个用户发了多少帖子，肯定会更快。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1176780|如果你在posts表的user字段上有一个索引，你也会得到不错的查询速度。但是，当您的posts表足够大时，它会损害您的数据库。如果你正在计划扩展，那么我绝对建议你保留特定领域的用户帖子的记录。|offset|length|style|CODE|1176781|entityMap^0|0|4|5|B|4|17|5|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@$D|L|E|M|F|G]|$D|N|E|O|F|G]|$D|P|E|Q|F|G]]|9|@]|A|$]]|$1|H|3|-4|5|6|7|R|8|@]|9|@]|A|$]]]|I|$]]

If you keep a record of how many post a user made, it will definitely be faster.

If you have an index on <code>user</code> field of <code>posts</code> table, you will get decent query speeds also. But it will hurt your database when your <code>posts</code> table is big enough. If you are planning to scale, then I would definitely recommend keeping record of users posts on a specific field.

blocks|key|3466115|text|存储预计算值是一种常见且简单但非常有效的优化方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3466116|因此，只需添加包含用户发布的评论数量的列，并使用触发器或您的应用程序维护它。|3466117|性能差异是：|3466118|3466119|使用COUNT(*)时，您将始终使用索引查找%2B结果计数|unordered-list-item|offset|length|style|CODE|3466120|使用附加字段，您将使用索引查找%2B返回数字(已有答案)。|3466121|3466122|在这种情况下，myisam和innodb之间没有明显的区别。|3466123|entityMap^0|0|0|0|0|2|8|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|G|3|H|5|I|7|Y|8|@$J|Z|K|10|L|M]]|9|@]|A|$]]|$1|N|3|O|5|I|7|11|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

Storing precalculated values is a common and simple, but very efficient sort of optimization.

So just add the column with amount of comments user has posted and maintain it with triggers or by your application.

The performance difference is:

<ul>
<li>With <code>COUNT(*)</code> you always will have index lookup + counting of results</li>
<li>With additional field you'll have index lookup + returning of a number (that already has an answer).</li>
</ul>

And there will be no significant difference between myisam and innodb in this case

blocks|key|1170657|text|存储帖子计数。不管存储引擎是什么，这似乎都是一个可伸缩性问题。您是否会在用户每次提交帖子时重新计算计数，还是会在webserver范围之外的某个地方运行一个作业来处理此负载？你的帖子数量是多少？您的服务器可以处理什么类型的负载？我真的不认为存储引擎会成为故障点。我说存储这个值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1170658|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Store the post count. It seems that this is a scalability question, regardless of the storage engine. Would you recalculate the count each time the user submitted a post, or would you run a job to take care of this load somewhere outside of the webserver sphere? What is your post volume? What kind of load can your server(s) handle? I really don't think the storage engine will be the point of failure. I say store the value.

blocks|key|4934940|text|post上的性能还是计数上的性能？从数据纯粹主义者的角度来看，记录的计数与实际计数不同。你可以看着礼堂的前门，把进来的人加起来，减去离开的人，但如果有人偷偷从后门进来怎么办？如果批量删除一个问题主题怎么办？如果你记录了计数，那么一个帖子就会减慢速度来计算和记录计数。对我来说，数据完整性就是一切，我会计算(星号)每一次。我刚刚在一个有3100万行的表上测试了一个索引列上的计数(星号)，这个值有424,887行-+1.4秒(在我的P4+2+GB开发机器上，因为我故意使用我的开发服务器，所以我会因为慢查询而受到惩罚-在生产的8核16+GB服务器上，计数不到0.1秒)。您永远不能保护数据不受程序逻辑中意外更改或错误的影响。Count(star)是计数，而且速度很快。如果count(star)很慢，那么在其他查询中就会出现性能问题。当符号引起了格式改变时，我确实开始了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4934941|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Performance on post or performance on performance on count? From a data purist perspective a recorded count is not the same as an actual count. You can watch the front door to an auditorium and add the people that come in and subtract those the leave but what if some sneak in the back door? What if you bulk delete a problem topic? If you record the count then the a post is slowed down to calculate and record the count. For me data integrity is everything and I will count(star) every time. I just did a test on a table with 31 million row for a count(star) on an indexed column where the value had 424,887 rows - 1.4 seconds (on my P4 2 GB development machine as I intentionally under power my development server so I get punished for slow queries - on the production 8 core 16 GB server that count is less than 0.1 second). You can never guard your data from unexpected changes or errors in your program logic. Count(star) is the count and it is fast. If count(star) is slow you are going to have performance issues in other queries. I did star as the symbol caused a format change.

blocks|key|1149852|text|如果您在user_id上有适当的索引，那么COUNT(user_id)就很简单了。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1149853|从语义上讲，这也是正确的方法。|1149854|entityMap^0|4|7|L|E|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|O|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|P|8|@]|D|@]|E|$]]]|I|$]]

If you have the proper index on <code>user_id</code>, then <code>COUNT(user_id)</code> is trivial.

It's also the correct approach, semantically.

blocks|key|1149871|text|这真的是一个“权衡”的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1149872|实际上，如果您的“posts”表在“UserID”列上有一个索引，而您真的只想返回每个用户的帖子数量，那么使用基于此列的查询应该会有很好的效果。|1149873|如果你对e‘g有另一个表'UserPosts’，是的，查询那个表会更快，但真正的问题是‘你的'Posts’表真的太大了吗，以至于你不能只查询这个计数。这两种方法的权衡显然是这样的：|1149874|1)有单独的审计表，则在添加、更新不具有单独的审计表的帖子时有开销2)然后在直接查询表时有开销|1149875|我的直觉总是设计一个系统，以一种合理的标准化方式记录数据。我从来不会基于这样一个事实来制作表格:为了报告目的，获取一些数据可能会更快。我只会创建它们，如果需要出现，并且有必要将它们合并在一起，我会将其合并。|1149876|归根结底，我认为除非你的“posts”表非常大(即超过几百万条记录)，否则查询它的不同用户计数应该没有问题，假设它的索引是正确的，即放在“UserID”列上的索引。|1149877|如果您仅将此信息用于显示目的(例如，用户jonny已经发布了73次)，那么当发生更改检测时，从DB中获取一次信息，缓存它，然后更新它(缓存)就足够简单了。|1149878|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|S|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|T|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|U|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|V|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|O|$]]

this is really one of those 'trade off' questions. 

Realistically, if your 'Posts' table has an index on the 'UserID' column and you are truly only wanting to return the number of posts pers user then using a query based on this column should perform perfectly well.

If you had another table 'UserPosts' for e'g., yes it would be quicker to query that table, but the real question would be 'is your 'Posts' table really so large that you cant just query it for this count. The trade off on both approaches is obviously this:

1) having a separate audit table, then there is an overhead when adding, updating a post
2) not having a separate audit table, then overhead in querying the table directly

My gut instinct is always to design a system to record the data in a sensibly normalised fashion. I NEVER make tables based on the fact that it might be quicker to GET some data for reporting purposes. I would only create them, if the need arised and it was essential to incoroporate them then, i would incorporate it.

At the end of the day, i think unless your 'posts' table is ridiculously large (i.e. more than a few millions of records, then there should be no problem in querying it for a distinct user count, presuming it is indexed correctly, i.e. an index placed on the 'UserID' column.

If you're using this information purely for display purposes (i.e. user jonny has posted 73 times), then it's easy enough to get the info out from the DB once, cache it, and then update it (the cache), when or if a change detection occurs.

blocks|key|1149893|text|有一大堆的权衡，所以没有人能给你正确的答案。但这里有一种其他人都没有提到的方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1149894|您可以使用"select+where“查询，但将结果缓存到更高层(例如memcache)。所以你的代码应该看起来像这样：|1149895|count+=+memcache.get('article-count-'+%2B+user_id)
if+count+is+None:
++++count+=+database.execute('select+.....+where+user_id+=+'+%2B+user_id)
++++memcache.put('article-count-'+%2B+user_id,+count)|code-block|syntax|javascript|1149896|此外，当用户发布新帖子时，您还需要|1149897|memcache.delete('article-count-'+%2B+user_id)|1149898|当文章计数经常使用，但很少更新时，这将是最好的。它结合了高效缓存的优点和规范化数据库的优点。但是，如果只需要很少的文章数，这不是一个好的解决方案(在这种情况下，优化是必要的吗？)。另一种不合适的情况是，经常需要某人的文章计数，但几乎总是另一个人。|1149899|这种方法的另一个优点是，您现在不需要添加缓存。您可以使用最简单的数据库设计，如果缓存此数据非常重要，则在以后添加缓存(不需要更改您的模式)。|1149900|更一般的情况是:您不需要在数据库中缓存。你也可以在你的数据库“周围”放一个缓存。例如，我对java所做的一些事情就是在ibatis级别使用缓存。|1149901|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|Z|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|11|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

there are a whole pile of trade-offs, so no-one can give you the right answer. but here's an approach no-one else has mentioned:

you could use the "select where" query, but cache the result in a higher layer (memcache for example). so you code would look like:

<pre><code>count = memcache.get('article-count-' + user_id)
if count is None:
 count = database.execute('select ..... where user_id = ' + user_id)
 memcache.put('article-count-' + user_id, count)
</code></pre>

and you would also need, when a user makes a new post

<pre><code>memcache.delete('article-count-' + user_id)
</code></pre>

this will work best when the article count is used often, but updated rarely. it combines the advantage of efficient caching with the advantage of a normalized database. but it is not a good solution if the article count is needed only rarely (in which case, is optimisation necessary at all?). another unsuitable case is when someone's article count is needed often, but it is almost always a different person.

a further advantage of an approach like this is that you don't need to add the caching now. you can use the simplest database design and, if it turns out to be important to cache this data, add the caching later (without needing to change your schema).

more generally: you don't need to cache in your database. you could also put a cache "around" your database. something i have done with java is to use caching at the ibatis level, for example.

I am building a forum and I am trying to count all of the posts submitted by each user. Should I use <code>COUNT(*) WHERE user_id = $user_id</code>, or would it be faster if I kept a record of how many posts each user has each time he made a post and used a SELECT query to find it?

How much of a performance difference would this make? Would there be any difference between using InnoDB and MyISAM storage engines for this?

COUNT(*) WHERE vs. SELECT(*) WHERE performance

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我正在建立一个论坛，我试图计算每个用户提交的所有帖子。我应该使用COUNT(*) WHERE user_id = $user_id，或者如果我记录每个用户每次发布帖子并使用SELECT查询查找帖子时有多少帖子，会更快吗？这会带来多大的性能差异？使用InnoDB和MyISAM存储引擎会有什么不同吗？

问计数() WHERE与SELECT() WHERE
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问计数(*) WHERE与SELECT(*) WHEREEN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问计数() WHERE与SELECT() WHERE
EN