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
としても同じ.
参考
- MySQL :: MySQL 5.7 Reference Manual :: 8.2.1.14 ORDER BY Optimization
- MySQL :: MySQL 5.7 Reference Manual :: 11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
- MySQL :: MySQL 5.7 Reference Manual :: 8.3.5 Multiple-Column Indexes
- MySQL :: MySQL 5.7 Reference Manual :: 13.1.18.8 CREATE TABLE and Generated Columns