ISUCON6 予選問題の解説を読んでいたところ,
キーワードリンクが最前最長マッチを期待している関係上、CHARACTER_LENGTH 順でソートしていますが、そこをvirtual columnなり別カラムなりにindexを張るのも良いでしょう。折角MySQL5.7なので、virtural columnを使うのがオシャレです。
とあったので,具体的にどうすればいいか試してみた.
virtual column を作る
まずindexを貼る対象となるvirtual columnを作る.
普通に ALTER TABLE
でカラムを追加するのに加えて AS (式)
でカラムの内容を指定してやるとよい.
カラムの型が必要なので適当に指定してやるとよい.TINYINT
でもいい気がしたけど念のため SMALLINT
にした.
mysql> ALTER TABLE entry ADD COLUMN keyword_length SMALLINT(8) AS (CHARACTER_LENGTH(keyword));
ソート用のインデックスを貼る
この時点ではまだソート用のindexは作られていないため,filesortが行われている.
mysql> EXPLAIN SELECT keyword FROM entry ORDER BY CHARACTER_LENGTH(keyword) DESC;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
| 1 | SIMPLE | entry | NULL | index | NULL | keyword | 767 | NULL | 5851 | 100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)
結論から言うと (ソート対象のカラムの文字数を表す virtual column, ソート対象のカラム)
となる複合indexを貼ってやるとよい.
mysql> CREATE INDEX keyword_length_and_keyword ON entry(keyword_length, keyword);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN SELECT keyword FROM entry ORDER BY CHARACTER_LENGTH(keyword) DESC;
+----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
| 1 | SIMPLE | entry | NULL | index | NULL | keyword_length_and_keyword | 770 | NULL | 5851 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------------------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
これで文字数順ソートにindexが効くようになった.ORDER BY keyword_length
としても同じ.
参考