mysql - 优化 Mysql UNION 查询以加快加载时间

Question

我是 mysql 的初学者，我需要一些帮助来加快以下查询的速度。我也是索引的初学者，不太明白如何添加和使用它们。如果有人可以帮助我加快这个查询的速度，我将非常感激。目前，查询加载大约需要 10 秒。

显示第 0 - 0 行(总共 1 行，查询耗时 10.2243 秒)

SELECT `location2` AS `location` , '2' AS `number`
FROM `properties`
WHERE `price_from`
BETWEEN 0
AND 999999
AND `country` = 'france'
UNION SELECT `location3` AS `location` , '3' AS `number`
FROM `properties`
WHERE `price_from`
BETWEEN 0
AND 999999
AND `location2` = 'france'
UNION SELECT `location4` AS `location` , '4' AS `number`
FROM `properties`
WHERE `price_from`
BETWEEN 0
AND 999999
AND `location3` = 'france'
UNION SELECT `location5` AS `location` , '5' AS `number`
FROM `properties`
WHERE `price_from`
BETWEEN 0
AND 999999
AND `location4` = 'france'
UNION SELECT `location6` AS `location` , '6' AS `number`
FROM `properties`
WHERE `price_from`
BETWEEN 0
AND 999999
AND `location5` = 'france'

Answer 1

不要跨列展开数组。相反，可以使用另一个表，其中包含所需的行数。

通过这一更改，您可以避免使用 UNION，我们可以讨论如何优化所需的 JOIN。