优化一个 UNION mysql 查询 [英] Optimize a UNION mysql query
问题描述
我正在使用 UNION 查询根据日期从两个表中提取数据.下面查询
I'm using a UNION query to extract data from two tables based on date. Query below
SELECT title, id, date as date_added
FROM test1 WHERE test1.id
UNION
SELECT title, customer as id, date_added
FROM test2 WHERE test2.id
ORDER BY date_added DESC LIMIT 0,8
我在每个表上都有一个关于 date 和 date_ added 的索引...问题是查询没有优化,当我使用 EXPLAIN 时,它显示两个表的所有行都被选中来给我输出.
I have an index on date and date_added on each table...the problem is that the query isn't optimized and when I use EXPLAIN it shows all the rows of both the tables are being selected to give me the output.
是否有其他方法可以执行此查询以对其进行优化?我能想到的唯一解决方案是使用 LIMITS 分别运行两个查询并对应用程序中的数据进行排序,但似乎无法在我的应用程序中执行分页.
Is there any other way of doing this query so that it can be optimized? The only solution i can think of is running the two queries separately with LIMITS and sorting the data in the application, but it seems it would be impossible to perform pagination in my application with that.
推荐答案
我不是最好的专家,但我会假设因为 UNION
mysql 需要选择和合并这两个结果在执行 ORDER
之前设置,因此,可能由 LIMIT
子句,不会发生.
I'm not the best expert, but I would assume that because of the UNION
mysql needs to select and merge the two result sets before it performs the ORDER
ing, and thus, the potential result set optimization which might be provided by the LIMIT
clause, doesn't happen.
也许这个问题也适用于您的情况:
Perhaps this question can apply to your case as well:
在 MySQL 查询中结合 UNION 和 LIMIT 操作
这篇关于优化一个 UNION mysql 查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!