MySQL :: senna / index 作成時の注意点

全文検索エンジン senna を導入した際の、index の張り方は少し特殊なので注意が必要。

• 通常のN-gramインデックス作成コマンド

create fulltext index インデックス名 using NGRAM on テーブル名 (カラム名);

例)
create fulltext index idx_fulltext using NGRAM on tablename (body);

• 1つのテーブルに複数のN-gramインデックスを作成する場合

ALTER TABLE tablename
  ADD FULLTEXT idx_fulltext_title USING NGRAM (title),
  ADD FULLTEXT idx_fulltext_body  USING NGRAM (body);

のように、1つのクエリで複数のインデックスを作成しなければならない。

ALTER TABLE tablename
  ADD FULLTEXT idx_fulltext_title USING NGRAM (title);

ALTER TABLE tablename
  ADD FULLTEXT idx_fulltext_body  USING NGRAM (body);

のように、2回のクエリでインデックスを張った場合、最初のインデックス(idx_fulltext_title)は無効になってしまう。

正しく動作するか確認してみる

SELECT * FROM tablename WHERE match(title) against('ほげ');
SELECT * FROM tablename WHERE match(body) against('ほげほげ');

• 検索対象にしたいカラムが複数ある場合

create fulltext index idx_fulltext using NGRAM on tablename(title, body);

上記ように複数カラムにまたがったインデックスを作成する。

正しく動作するか確認してみる

SELECT * FROM tablename WHERE match(title, body) against('ほげ');

• NGRAMインデックスが張られているか確認してみる

mysql> show index from fulltext;
+---------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------------+---------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type       | Comment |
+---------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------------+---------+
| fulltext|          1 | idx_hoge1 |            1 | hoge1        | A         |       30024 |     NULL | NULL   |      | BTREE            |         |
| fulltext|          1 | idx_hoge2 |            1 | hoge2        | NULL      |           1 |     NULL | NULL   |      | FULLTEXT,SENNA,N |         |
| fulltext|          1 | idx_hoge3 |            1 | hoge3        | NULL      |           1 |     NULL | NULL   |      | FULLTEXT,SENNA,N |         |
+---------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------------+---------+

Index_type が「FULLTEXT,SENNA,N」であればOK。