サジェストがうまく取れない場合があったので抵抗してみたよ

読む前に

この記事を読む前に、拙著の記事MySQL5.6で全文検索やったよをご確認いただく必要はございません。。

や、時系列的には↑の後なんだけど、まー・・・・アレです、関係あんだかないんだかは不明です。

サジェスト機能つくったよ

MySQL5.6の全文検索をつかって、入力した文字列からそれっぽいのを検索して返す機能をつくりました。
まぁ、デバッグ時点で色々あったものの、逐一対応して作り上げたわけです。
それなりにうまく動いてるなぁ、と思ってたら・・・・

先方（以下、客）「なんかサジェストがうまく動いてないんですが・・・・(;・∀・)」
ME「え？でもちゃんと動いてるっぽいんですが・・・・。どういうときですかね？」
客「さー・・・・でも動いてないって言われるんですよね(^-^;)」
ME「えー・・・・(;´Д｀)」

ほんでどーしたかっていうと

まー調査ですよね。。。

で、分かったんですよ問題が。

問題

サジェストは、ちょっと業務的な問題もあるんで簡単に書くと、だいたいこうなってました。

テーブルはこんな感じ。

create table if not exists `t_suggests` (
  `id` int(12) unsigned not null auto_increment comment 'ID',
  `keyword` varchar(30) not null comment 'キーワード',
  `keyword_ngram` varchar(90) not null comment 'キーワード（N-gram）',
  `use_num` int(12) unsigned not null default '0' comment '使用回数',
  primary key (`id`)
) ENGINE=InnoDB COLLATE `utf8_unicode_ci` COMMENT='サジェスト';

create FULLTEXT index idx_t_suggests_1 on t_suggests (`keyword_ngram`);

で、検索する際のクエリーは↓

SELECT *
  FROM `t_suggests`
 WHERE MATCH (keyword_ngram) AGAINST ('"キーワード"' IN BOOLEAN MODE)
 ORDER BY use_num DESC
 LIMIT 0, 5;

あ、検索用カラムはBi-gramです。

今回、先方の要望だったかなんだったかは忘れたけど、「サジェストで使われた回数の多い順で出してほしい」てのがあったんですな。
だから、検索時には使用回数（use_num）の降順で取得していたワケ。
なお、先頭5件も先方指定。ま、ズラズラ出されても邪魔だしコレは分かる。

が、このせいで問題が発生していたんですよ、奥さん。

例えば、こんなデータを入れていたとしましょう。

INSERT INTO `t_suggests` VALUES
 (null, 'チョウ', 'チョ ョウ', 2)
,(null, 'モンシロチョウ', 'モン ンシ シロ ロチ チョ ョウ', 10)
,(null, 'チョッパー', 'チョ ョッ ッパ パー ', 0)
,(null, 'モンキチョウ', 'モン ンキ キチ チョ ョウ ', 6)
,(null, 'ちょうちょ', 'ちょ ょう うち ちょ', 4)
,(null, 'アゲハチョウ', 'アゲ ゲハ ハチ チョ ョウ', 6)
,(null, 'キアゲハ', 'キア アゲ ゲハ', 0)
,(null, 'クロアゲハ', 'クロ ロア アゲ ゲハ ', 4)
,(null, 'チョウシュウリキ', 'チョ ョウ ウシ シュ ュウ ウリ リキ', 21)
,(null, 'オオムラサキ', 'オオ オム ムラ ラサ サキ ', 9)
,(null, 'みょうちょう', 'みょ ょう うち ちょ ょう', 0)
,(null, 'コノハチョウ', 'コノ ノハ ハチ チョ ョウ ', 1)
,(null, 'ショウシュウリキ', 'ショ ョウ ウシ シュ ュウ ウリ リキ', 5)
,(null, 'ちょうみりょう', 'ちょ ょう うみ みり りょ ょう', 0)
,(null, 'とうきょうとっきょきょかきょく', 'とう うき きょ ょう うと とっ っき きょ ょき きょ ょか かき きょ ょく', 4)
,(null, 'イチョウ', 'イチ チョ ョウ', 4)
,(null, 'あぶらあげ', 'あぶ ぶら らあ あげ', 7)
,(null, 'カンリチョウ', 'カン ンリ リチ チョ ョウ', 8)
,(null, 'ウスバカゲロウ', 'ウス スバ バカ カゲ ゲロ ロウ', 4)
,(null, 'ちょうちょうふじん', 'ちょ ょう うち ちょ ょう うふ ふじ じん', 7)
,(null, 'ちじょう', 'ちじ じょ ょう', 3)
,(null, 'ちょっこうびん', 'ちょ ょっ っこ こう うび びん', 3)
,(null, 'ちょくじょうけいこう', 'ちょ ょく くじ じょ ょう うけ けい いこ こう', 3)

なんか蝶々多めのデータですが、気にしない（ルパン、まどマギでこの手のデータが思いつかなかっただけです）。

これで先のSQLにより「チョウ」を検索しようとすると・・・・

mysql> SELECT *
    ->   FROM `t_suggests`
    ->  WHERE MATCH (keyword_ngram) AGAINST ('"チョ ョウ"' IN BOOLEAN MODE)
    ->  ORDER BY use_num DESC
    ->  LIMIT 0, 5;
+----+-----------------------------+---------------------------------------------------------+---------+
| id | keyword                     | keyword_ngram                                           | use_num |
+----+-----------------------------+---------------------------------------------------------+---------+
|  9 | チョウシュウリキ            | チョ ョウ ウシ シュ ュウ ウリ リキ                      |      21 |
|  2 | モンシロチョウ              | モン ンシ シロ ロチ チョ ョウ                           |      10 |
| 18 | カンリチョウ                | カン ンリ リチ チョ ョウ                                |       8 |
| 20 | ちょうちょうふじん          | ちょ ょう うち ちょ ょう うふ ふじ じん                 |       7 |
|  4 | モンキチョウ                | モン ンキ キチ チョ ョウ                                |       6 |
+----+-----------------------------+---------------------------------------------------------+---------+
5 rows in set (0.02 sec)

あら不思議、「チョウ」が出てこない・・・・(;´Д｀)

原因

まぁ、お分かりだと思いますが・・・・。

先のクエリーからLIMITを取ってみる。

mysql> SELECT *
    ->   FROM `t_suggests`
    ->  WHERE MATCH (keyword_ngram) AGAINST ('"チョ ョウ"' IN BOOLEAN MODE)
    ->  ORDER BY use_num DESC;
+----+-----------------------------+---------------------------------------------------------+---------+
| id | keyword                     | keyword_ngram                                           | use_num |
+----+-----------------------------+---------------------------------------------------------+---------+
|  9 | チョウシュウリキ            | チョ ョウ ウシ シュ ュウ ウリ リキ                      |      21 |
|  2 | モンシロチョウ              | モン ンシ シロ ロチ チョ ョウ                           |      10 |
| 18 | カンリチョウ                | カン ンリ リチ チョ ョウ                                |       8 |
| 20 | ちょうちょうふじん          | ちょ ょう うち ちょ ょう うふ ふじ じん                 |       7 |
|  4 | モンキチョウ                | モン ンキ キチ チョ ョウ                                |       6 |
|  6 | アゲハチョウ                | アゲ ゲハ ハチ チョ ョウ                                |       6 |
|  5 | ちょうちょ                  | ちょ ょう うち ちょ                                     |       4 |
| 16 | イチョウ                    | イチ チョ ョウ                                          |       4 |
|  1 | チョウ                      | チョ ョウ                                               |       2 |
| 12 | コノハチョウ                | コノ ノハ ハチ チョ ョウ                                |       1 |
| 11 | みょうちょう                | みょ ょう うち ちょ ょう                                |       0 |
| 14 | ちょうみりょう              | ちょ ょう うみ みり りょ ょう                           |       0 |
+----+-----------------------------+---------------------------------------------------------+---------+
12 rows in set (0.00 sec)

どうやら「チョウ」は9番目らしい。
正確には、use_numの多い順の9番目。

そう、use_numの降順で指定しているせい。
MATCH...AGAINST...での検索は、通常だと検索キーワードの関連性の降順に並ぶのだけど、これはあくまで並び順を指定していない場合の話。
それを余計なモンいれてるせいで、「チョウ」が取れなくなってしまっていた。

対応（間違い）

原因が分かればこっちのもの。
ほんなら並び順にそれ指定してやればいいんだべさ(^q^)、とやってみる。

mysql> SELECT *
    ->   FROM `t_suggests`
    ->  WHERE MATCH (keyword_ngram) AGAINST ('"チョ ョウ"' IN BOOLEAN MODE)
    ->  ORDER BY MATCH (keyword_ngram) AGAINST ('"チョ ョウ"' IN BOOLEAN MODE) DESC, use_num DESC;
+----+-----------------------------+---------------------------------------------------------+---------+
| id | keyword                     | keyword_ngram                                           | use_num |
+----+-----------------------------+---------------------------------------------------------+---------+
| 20 | ちょうちょうふじん          | ちょ ょう うち ちょ ょう うふ ふじ じん                 |       7 |
|  5 | ちょうちょ                  | ちょ ょう うち ちょ                                     |       4 |
| 11 | みょうちょう                | みょ ょう うち ちょ ょう                                |       0 |
| 14 | ちょうみりょう              | ちょ ょう うみ みり りょ ょう                           |       0 |
|  9 | チョウシュウリキ            | チョ ョウ ウシ シュ ュウ ウリ リキ                      |      21 |
|  2 | モンシロチョウ              | モン ンシ シロ ロチ チョ ョウ                           |      10 |
| 18 | カンリチョウ                | カン ンリ リチ チョ ョウ                                |       8 |
|  4 | モンキチョウ                | モン ンキ キチ チョ ョウ                                |       6 |
|  6 | アゲハチョウ                | アゲ ゲハ ハチ チョ ョウ                                |       6 |
| 16 | イチョウ                    | イチ チョ ョウ                                          |       4 |
|  1 | チョウ                      | チョ ョウ                                               |       2 |
| 12 | コノハチョウ                | コノ ノハ ハチ チョ ョウ                                |       1 |
+----+-----------------------------+---------------------------------------------------------+---------+
12 rows in set (0.00 sec)

事☆態☆悪☆化
まさかのランクダウン、11番目。。。(ノД`)・゜・。

ま、これも落ち着いて考えれば当たり前のこと。

MATCH...AGAINST...は、それ自体はキーワードに対する各データの関連性を返す関数。
で、この関連性というヤツは、キーワードに関連する全体レコードのうち、対象レコードがキーワードを保有する割合で求められている（日本語が難しい・・・・）。
なので、今回の場合だと「ちょうちょうふじん」の方が「チョ ョウ」というキーワードを保有している割合が「チョウ」より多いので、先頭に来ている。

うへぇ・・・・(;´･ω･)

対応（Now）

んじゃーどーすっかなぁ、と考えた結果。
こーした↓

mysql> SELECT *
    ->   FROM `t_suggests`
    ->  WHERE MATCH (keyword_ngram) AGAINST ('"チョ ョウ"' IN BOOLEAN MODE)
    ->  ORDER BY CHAR_LENGTH(keyword), use_num DESC
    ->  LIMIT 0, 5;
+----+--------------------+-------------------------------------+---------+
| id | keyword            | keyword_ngram                       | use_num |
+----+--------------------+-------------------------------------+---------+
|  1 | チョウ             | チョ ョウ                           |       2 |
| 16 | イチョウ           | イチ チョ ョウ                      |       4 |
|  5 | ちょうちょ         | ちょ ょう うち ちょ                 |       4 |
| 18 | カンリチョウ       | カン ンリ リチ チョ ョウ            |       8 |
|  4 | モンキチョウ       | モン ンキ キチ チョ ョウ            |       6 |
+----+--------------------+-------------------------------------+---------+
5 rows in set (0.00 sec)

もうどーしようもないんで、文字数の昇順で並び替えを最初に指定した。
サジェストである以上、合致するもしないも、文字数が小さいのが先に来て問題ないハズ、てことで。
とりあえず、これで現状は問題がない・・・・みたい（まPJ的な最終判断は週明けなんだけどさ）。先方からもOKもらえたんでしばらくはコレで運用してみます。

とはいえ・・・・

なんか他にスマートでベターな方法があれば教えてください(ノД`)・゜・。