MySQL: SELECTの結果をUNIONして ORDER BYする際の最適化方法

SELECTの結果をUNIONして ORDER BY する際には下記の点に注意する必要があります。

• 無駄なメモリ消費
• ソートにINDEXが利かない (≒CPU負荷増大)

対応策

可能であればPush-down Condition (各サブクエリ内でORDER BY, LIMIT, OFFSETを適用してからUNION, ORDER BYを実行する)を利用することで、 パフォーマンスを改善できる場合があります。
下記に例を示します。

もともとのクエリ

SELECT
tmp.*
FROM
(
SELECT

tableA.column1,
tableA.column2
FROM tableA
WHERE (条件)

UNION ALL

SELECT
tableB.column1,
tableB.column2
FROM tableB
WHERE (条件)
) AS tmp
ORDER BY tmp.column1, tmp.column2
LIMIT 100, 20 

Push-down Conditionを用いて書き直したクエリ

SELECT
tmp.*
FROM
(
SELECT

tableA.column1,
tableA.column2
FROM tableA
WHERE (条件)
ORDER BY tableA.column1, tableA.column2
LIMIT 30 # <- 10 (offset) + 20 (limit)

UNION ALL

SELECT
tableB.column1,
tableB.column2
FROM tableB
WHERE (条件)
ORDER BY tableB.column1, tableB.column2
LIMIT 30 # <- 10 (offset) + 20 (limit)

) AS tmp
ORDER BY tmp.column1, tmp.column2
LIMIT 10, 20 

ただしこのPush-down Conditionの手法も下記の場合は、効果が半減しますので注意が必要です。

• OFFSETの値が大きい場合は、結局全結果セットUNIONと変わらない
• サブクエリ内のソートで、INDEXが効かない場合