我想知道如何使这种类型的查询更快。
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' ;