SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

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文字にカットできないためで……。

 半角カナはアプリ側で禁止(変換)しても良いかと思うのですが、携帯から入力する人は気軽に使うので微妙ですね。