Mysql活动联合查询
经营权。 我有一个活动提要，它从网站的不同部分获取所有不同类型的活动，并通过使用 UNION 和 ORDER BY 以及 LIMIT 来对它们进行排序，以获得前 25 个活动，然后显示活动。
我的程序员同事说，当我们有更多行时（目前我们有 800 行），我们会遇到问题，但没关系。
所以问题来了。 UNION 会导致以后的速度变慢吗？ 如果是这样我们应该 a) 尝试将活动放入新表中，然后对其进行查询。 b) 尝试某种视图？ -（如果是的话，谁能解释一下我不太确定怎么做！） c) 其他...
感谢您的帮助。 理查德
right to business.
I have an activity feed which gets all different kinds of activity from different parts of my site sorts them all out by means of using UNION and an ORDER BY and then a LIMIT to get the top 25 and then displays the activity.
My fellow programmer says that we will run into problems when we have more rows (currently we have 800) and it's fine.
So the question.
Will the UNION cause slow down later down the line?
If so should we
a) Try and put the activity into a new table and then query that.
b) Try some sort of view? - (if so could anyone explain how I'm not too sure how!)
c) Other...
Thanks for your help.
Richard
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
为什么不将每个单独的查询也限制为 25 个呢？这样您就可以限制在合并之前返回的行数并限制最终列表
Why not just limit each of the individual queries to 25 as well? That way you could restrict the amount of rows that come back before being unioned and limited for the final list
这是一个棘手的问题，因为它取决于很多变量，例如您的桌子有多忙等。
我想
union稍后会减慢您的速度，因为您使用的方法基本上是在做整个表的并集，因此需要在应用排序和行数限制之前将它们读入内存。随着数据量的增加，您的查询将会变慢。如果表中的所有数据都很重要，那么您最好尝试并确保尽可能地对表进行索引，这样至少您的排序运行得很快等等。如果表中的某些数据变旧或过时，并且您对它不太感兴趣，那么您可能有范围只将需要的行读入临时表。然后可以订购等等。
This is a tricky one as it depends on a lot of variables, such as how busy your tables are etc.
I would imagine the
unionwould slow you down later, as the method you are using is basically doing a union on entire tables so these need to be read into memory before the ordering and the limiting of the number of rows is applied. Your query will get slower as the amount of data increases.If all the data in the tables is important then the best you can do it try and ensure you index the tables as best you can so at least your ordering runs fast etc. IF some of the data in the tables gets old or stale and you aren't too interested in it, then you might have scope to read just the rows you need into a temp table. This can then be ordered etc.
我认为最好的方法可能是结合使用 2 种方法，因此
a)yes Index
b) 然后对每个子查询执行 LIMIT 25。
c) 对每个查询执行 where added_date >= date 操作，以便我们获得正确的日期顺序
嗯，但这会带来关于去什么日期的问题。因此，如果我们转到第 x 页，我会得到哪些日期。
这正在变成一个问题，而且是一个很大的问题。我们拥有的数据量将会非常大。
感谢您的帮助。
理查德
I think the best way might be to do a combination of the 2 so
a)yes Index
b) then do the LIMIT 25 on each of the sub queries.
c) Do a where added_date >= date on each of the queries so that we have the correct date order
mmm but then that presents problems as to what dates to go for. So if we go to page x which dates do I get.
This is turning into a problem and quite a big one. The size of data we have is going to be quite big.
Thanks for your help.
Richard
决定只做一个日志并这样做。
一如既往地感谢您的帮助。
理查德
Decided just to make a log and do it that way.
Thanks as always for your help.
Richard