优化一个 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 ORDERing, 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屋！