MySQL性能–多个查询还是一个低效查询? [英] MySQL performance – Multiple queries or one inefficient query?

问题描述

我有三个表，每个表包含一些公用信息，以及一些表独有的信息. 例如:uid，date在表之间是通用的，但是一个表可以包含列type，而另一个表可以包含currency. 我需要查询数据库并获取在所有三个表中输入的最后20个条目(date DESC). 我的选择是:

I have three tables, each contain some common information, and some information that is unique to the table. For example: uid, date are universal among the tables, but one table can contain a column type while the other contains currency. I need to query the database and get the last 20 entries (date DESC) that have been entered in all three tables. My options are:

1. 一次查询数据库，一次大型查询，其中包含三个UNION ALL子句，并为列IE传递假值:

1. Query the database once, with one large query, containing three UNION ALL clauses, and pass along fake values for columns, IE:

FROM    (
    SELECT  uid, date, currency, 0, 0, 0

以及以后的

FROM    (
    SELECT  uid, date, 0, type, 0, 0

这会让我分配空值字段..

This would leave me with allot of null-valued fields..

或者我可以查询数据库3次，并且可以通过PHP sort在某种程度上通过信息查询最新的20个帖子.这将给我留下过多的信息-60个帖子以供浏览(LIMIT 20)* 3-并迫使我每次都进行某种类型的其他快速排序.

OR I can query the database three times, and somehow within PHP sort through the information to get the combined latest 20 posts. This would leave me with an excess of information - 60 posts to look through (LIMIT 20) * 3 - and force me to preform some type of addtional quicksort every time.

哪个选项更好/其他替代想法?

What option is better/any alternate ideas?

谢谢.

推荐答案

这两个选项比您听起来更相似.

Those two options are more similar than you make it sound.

当您使用UNION执行单个大型查询时，MySQL仍将执行三个单独的查询，就像您在替代计划中建议的那样，然后将它们组合为单个结果.

When you perform the single large query with UNIONs, MySQL will still be performing three separate queries, just as you propose doing in your alternative plan, and then combining them into a single result.

因此，您可以让MySQL为您进行过滤(和LIMIT)，也可以自己进行过滤.有了这种选择，让MySQL完成所有工作听起来是更可取的.

So, you can either let MySQL do the filtering (and LIMIT) for you, or you can do it yourself. Given that choice, letting MySQL do all the work sounds far preferable.

从理论上说，结果集中有多余的列可能会影响性能，但是如果结果集很小(如20行)，我希望它不会产生任何可检测的影响.

Having extra columns in the result set could theoretically hinder performance, but with so small a result set as your 20 rows, I wouldn't expect it to have any detectable impact.

这篇关于MySQL性能–多个查询还是一个低效查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！