Joruri でタグ検索が遅いと感じたら

この記事は公開されてから7年経過しています。現在とは内容が異なる場合がありますのでご注意ください。
最新の記事や関連記事をお探しの方は下の「サイト内検索」で検索してみてください。

Joruri とは徳島県で作られた Ruby on Rails を利用したCMSです。地方自治体向けのCMSとして人気があります。
で、使っているとタグ検索のときにかなりレスポンスが遅いなと感じることがありましたので、ちょっと調べてみました。

article_tags テーブルの word カラムにインデックスを張ると速くなりそう。

word カラムは text 型なのでインデックスを張るときに文字列長を指定しないといけません。文字列長を指定せずに行うと次のようにエラーになります。

mysql> create index i_article_tags_word on article_tags (word);
ERROR 1170 (42000): BLOB/TEXT column 'word' used in key specification without a key length

1 2	mysql> create index i_article_tags_word on article_tags (word); ERROR 1170 (42000): BLOB/TEXT column 'word' used in key specification without a key length

基本的にタグに使う単語は短いとおもうので、

mysql> create index i_article_tags_word on article_tags (word(8));

1	mysql> create index i_article_tags_word on article_tags (word(8));

とかでも大丈夫ですが、いまのすべてタグで一番長い単語をさがすには次のSQLで調べることができます。

mysql> select char_length(word) as len, word from article_tags order by len desc limit 1;
+------+--------------------+
| len  | word               |
+------+--------------------+
|    6 | ウォーキング        |
+------+--------------------+
1 row in set (0.00 sec)

mysql> select char_length(word) as len, word from article_tags order by len desc limit 1;

+------+--------------------+

| len | word |

+------+--------------------+

| 6 | ウォーキング |

+------+--------------------+

1 row in set (0.00 sec)

これだと、6文字の「ウォーキング」というタグが一番長いということがわかります。
なので、次のような create index 文になります。

mysql> create index i_article_tags_word on article_tags (word(6));

1	mysql> create index i_article_tags_word on article_tags (word(6));

それか今後増えるであろう長めのタグのことを考えて

mysql> create index i_article_tags_word on article_tags (word(12));

1	mysql> create index i_article_tags_word on article_tags (word(12));

とかでもいいと思います。

インデックスを張る前と後でSQLの実行時間を比べてみました。
インデックスを張る前

mysql> select unid from article_docs where exists (select * from article_tags where article_docs.unid = article_tags.unid and word like 'ウォーキング%');

1	mysql> select unid from article_docs where exists (select * from article_tags where article_docs.unid = article_tags.unid and word like 'ウォーキング%');

3回行った結果です。
7 rows in set (7.60 sec)
7 rows in set (7.65 sec)
7 rows in set (7.57 sec)

インデックスを張る

mysql> create index i_article_tags_word on article_tags (word(12));
Query OK, 1448 rows affected (0.10 sec)
Records: 1448  Duplicates: 0  Warnings: 0

mysql> create index i_article_tags_word on article_tags (word(12));

Query OK, 1448 rows affected (0.10 sec)

Records: 1448 Duplicates: 0 Warnings: 0

インデックスを張った後

mysql> select unid from article_docs where exists (select * from article_tags where article_docs.unid = article_tags.unid and word like 'ウォーキング%');

1	mysql> select unid from article_docs where exists (select * from article_tags where article_docs.unid = article_tags.unid and word like 'ウォーキング%');

3回行った結果です。
7 rows in set (0.32 sec)
7 rows in set (0.24 sec)
7 rows in set (0.23 sec)

実に 20分の1 まで速くなりました。

条件:
article_docs の件数: 2800強
article_tags の件数: 1500弱

とここまで長く書いておきながらいまさらですが、
article_docs と article_tags を join している unid カラムにもインデックスが張ってなかったので、試しにインデックスを張ったら更に早くなりました。
むしろ、こっちをやれば word カラムのインデックスは不要かも。

mysql> create index i_article_tags_unid on article_tags (unid);
Query OK, 1448 rows affected (0.22 sec)
Records: 1448  Duplicates: 0  Warnings: 0

mysql> create index i_article_docs_unid on article_docs (unid);
Query OK, 2844 rows affected (2.73 sec)
Records: 2844  Duplicates: 0  Warnings: 0

mysql> select unid from article_docs where exists (select * from article_tags where article_docs.unid = article_tags.unid and word like 'ウォーキング%');

mysql> create index i_article_tags_unid on article_tags (unid);

Query OK, 1448 rows affected (0.22 sec)

Records: 1448 Duplicates: 0 Warnings: 0

mysql> create index i_article_docs_unid on article_docs (unid);

Query OK, 2844 rows affected (2.73 sec)

Records: 2844 Duplicates: 0 Warnings: 0

mysql> select unid from article_docs where exists (select * from article_tags where article_docs.unid = article_tags.unid and word like 'ウォーキング%');

7 rows in set (0.01 sec)
7 rows in set (0.02 sec)
7 rows in set (0.01 sec)

インデックスなしの時の 700分の1 になりました、激速です。