MySQL の filesort プチテクニック

MySQL のチューニング関連のドキュメントを読んでいると「ORDER BY を避けろ」と書いてあるけど、できない (or したくない) 場合もあるわけで。そういう時はソート用の表と表示用の表を分割し自己結合することで、高速化できることもあります。適当な例ですが、

mysql> SHOW CREATE TABLE testt\G
*************************** 1. row ***************************
       Table: testt
Create Table: CREATE TABLE `testt` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `priority` int(10) unsigned NOT NULL,
  `data` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM testt;
+----------+
| COUNT(*) |
+----------+
|   100000 | 
+----------+
1 row in set (0.04 sec)

mysql> SELECT * FROM testt ORDER BY priority DESC,id DESC LIMIT 10;
(略)
10 rows in set (0.20 sec)

mysql> SELECT t1.*,t2.data FROM (SELECT id,priority FROM testt ORDER BY priority DESC,id DESC LIMIT 10) AS t1 INNER JOIN testt AS t2 ON t1.id=t2.id;
(略)
10 rows in set (0.09 sec)

と、最小限の列のみで filesort を行った後に自己結合する方が２倍以上速かったり。

MySQL の場合、一般論として、

• ホットスポットのデータセットの最小化を意識して損はない
• (filesort 等で生成されるものを含め) オンメモリのテンポラリテーブルは固定長にすべき

というのはあると思います。