MYSQL:查询2表与union非常慢，如何改进?

I want to query 2 tables with (almost) identical rows at the same time. As a result, I want to get the 5 recent entries (ordered by date, in total), no matter from which table they are from

我想同时用(几乎)相同的行查询两个表。因此，我希望获得最近的5个条目(按日期顺序排列)，无论它们来自哪个表。

So far, I tried this:

到目前为止，我尝试过:

SELECT date, name, text FROM `table_A` 
UNION 
SELECT date, name, text FROM `table_B` ORDER BY date desc LIMIT 5

Unfortunately, this query takes about 20 seconds (both tables have ~300.000 rows).

不幸的是，这个查询大约需要20秒(两个表都有大约300.000行)。

When I just do:

当我做的事:

SELECT date, name, text FROM `table_A` ORDER BY date desc LIMIT 5

或

SELECT date, name, text FROM `table_B` ORDER BY date desc LIMIT 5

the query takes only a few milliseconds.

查询只需要几毫秒。

So my question is: How can I improve my query to be faster or what select query should I use to get the 5 latest rows from both tables?

所以我的问题是:如何提高查询速度，或者使用什么select查询从两个表中获取最新的5行?

1 个解决方案

#1

Select the most recent 5 rows in each table before combining them.

在组合它们之前，在每个表中选择最近的5行。

SELECT *
FROM (
    (SELECT date, name, text FROM table_A ORDER BY date DESC LIMIT 5) 
    UNION
    (SELECT date, name, text FROM table_B ORDER BY date DESC LIMIT 5)
) x
ORDER BY date DESC
LIMIT 5

The problem with your query is that it's first merging the entire tables and removing duplicates before doing the ordering and limiting. The merged table doesn't have an index, so that part is slow.

您的查询的问题是，它首先合并整个表并在执行排序和限制之前删除重复项。合并的表没有索引，所以这个部分很慢。

#1