更好的做法：是否将计数结果保存到字段中？
我正在开发一个音乐流媒体网站，其中有两个主要表格：“活动”和“音乐”。除其他外，Activity 将每首歌曲的再现保存到新记录中。
每次我从音乐中选择时，我都需要获取每首歌曲的重复次数。那么，更好的做法是什么？
SELECT music.song, music.artist, COUNT (activity.id) AS reproductions
FROM music LEFT JOIN activity USING (song_id) WHERE music.song_id = XX
GROUP BY music.song_id
或者将复制品的数量保存到音乐表中的一个新字段中并查询：
SELECT song, artist, reproductions FROM music WHERE music.song_id = XX
当然，最后一个查询要容易得多。但要使用它，每次播放声音文件时，我都应该进行两个查询：一个在活动表中插入，另一个在音乐表的再现字段中更新。
在这种情况下更好的做法是什么？
I'm devloping a music streaming site where I have two major tables: 'activity' and 'music'. Activity saves, among other things, every song reproduction into a new record.
Every time I select from music I need to fetch the number of reproductions of every song. So, what would be the better practice
SELECT music.song, music.artist, COUNT (activity.id) AS reproductions
FROM music LEFT JOIN activity USING (song_id) WHERE music.song_id = XX
GROUP BY music.song_id
Or would it be better to save the number of reproductions into a new field in the music table and query this:
SELECT song, artist, reproductions FROM music WHERE music.song_id = XX
This last query is, of course, much easier. But to use it, every time I play a soundfile I should make two querys: one INSERT in the activity table, and one UPDATE on the reproductions field on music table.
What would be the better practice in this scenario?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这取决于这两个查询的响应时间。
当表变得巨大（假设）之后，sql nr 2 会更好。
您必须考虑到，即使插入也可能是昂贵的……如果数据库中有数百万行，您可能会考虑一些数据仓库。
Well this depends on the response times these two queries will have in time.
After tables will become huge (hypothetically) sql nr 2 will be better.
You have to think that in time even insert might be costly...you you might think on some data warehousing if you will have ..millions of rows in DB.