我想知道如何使这种类型的查询更快。

SELECT * FROM

(
  SELECT * FROM table1 WHERE DATE_FORMAT(date, '%Y-%m-%d') BETWEEN 'someDate' AND 'someDate'
  UNION ALL
  SELECT * FROM table2 WHERE DATE_FORMAT(date, '%Y-%m-%d') BETWEEN 'someDate' AND 'someDate'
)as unitable

最佳答案

首先,重写查询而不包含子查询。其次,更改日期比较,这样您就不会在日期列上使用任何函数(常量上的函数就可以了)。第三,在日期列上添加索引:

CREATE INDEX idx_table1_date on table1(date);
CREATE INDEX idx_table2_date on table2(date);

SELECT *
ROM table1
WHERE date BETWEEN 'someDate' AND 'someDate'
UNION ALL
SELECT *
FROM table2
WHERE date BETWEEN 'someDate' AND 'someDate' ;

09-10 12:07