私が歌川です

@utgwkk が書いている

MySQLで特定カラムの文字数順ソートにindexを効かせる

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 としても同じ.

参考