MySQLで全文検索2
前回(MySQLで全文検索1)の続き。
my.cnf(my.ini)の調整
FullTextIndex でインデックスされる最小の文字数を確認する。
MySQL5.5以下
mysql> SHOW VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_min_word_len | 4 | +-----------------+-------+
MySQL5.6 では innodb_ft_min_token_size に変更されている様子(デフォルトは3)。
mysql> SHOW VARIABLES like 'innodb_ft_min_token_size'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | innodb_ft_min_token_size | 3 | +-----------------+-------+
それぞれが、2以上のとき、my.cnf(my.ini)で ft_min_word_len を追加(変更)し、MySQLを再起動する。
[mysqld] #・・・に下記を追加追加(変更)。 ft_min_word_len=2 innodb_ft_min_token_size=2
再起動後、変わっているか確認。
mysql> SHOW VARIABLES like 'ft_min_word_len'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | ft_min_word_len | 2 | +-----------------+-------+ mysql> SHOW VARIABLES like 'innodb_ft_min_token_size'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | innodb_ft_min_token_size | 2 | +-----------------+-------+
検索用のカラムを作成しインデックスを作る
検索用のカラムを追加する。
例えば、以下の様なテーブルに
CREATE TABLE address ( address_id smallint(5) unsigned NOT NULL AUTO_INCREMENT, address varchar(50) NOT NULL, address2 varchar(50) DEFAULT NULL, district varchar(20) NOT NULL, city_id smallint(5) unsigned NOT NULL, postal_code varchar(10) DEFAULT NULL, phone varchar(20) NOT NULL, last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (address_id), KEY idx_fk_city_id (city_id), ) ;
searchというカラムを追加する。
ALTER TABLE address ADD COLUMN search TEXT NULL AFTER last_update;
追加したカラムに全文検索したいカラムの内容を繋いで、nGram でカットして更新する。
(あまり一度に更新されないように、WHERE句で絞りながら行ってください)
UPDATE address SET search = nGram( CONCAT(IFNULL(address), ' ', IFNULL(address2)) ) WHERE -- 絞りながら行ってください。 ;
search カラムに FullTextIndex を作成する。
ALTER TABLE address ADD FULLTEXT(search);
全文検索を行う
ヒット率順に出力。
※ ただし、全体の50%を超えたら出力されない。
SELECT * FROM address WHERE MATCH(search) against(ngram('住之江区 HOGEマンション'));
ヒット率を無視して出力
SELECT * FROM address WHERE MATCH(search) against(ngram('住之江区 HOGEマンション') in boolean mode );
というような形で、Like検索よりは高速に検索することができます。
感想
本当にビッグデータのときは、groonga などを利用する方が良いと思いますけれど……。
アプリケーション側で2文字ずつにカットするよりは、DBで行った方がデータの正規化が容易で、トリガーを入れておけばメンテフリーになります。
異字体は名前、住所をユーザが入れるシステムでは、正規化しておいた方が良いと思います。
今回、初めて MySQL のストアドプロシージャ(ファンクション)を書きましたが、シンプルである程度は使えると思いました。ちなみに、半角カナを変換しているのは utf8_unicode_ci にしていれば不要とも言えるのですが、全角に直さないと2文字にカットできないためで……。
半角カナはアプリ側で禁止(変換)しても良いかと思うのですが、携帯から入力する人は気軽に使うので微妙ですね。