【実験】MySQLのlike検索とfulltext検索をTwitterのつぶやき本文で比較してみた。#（ハッシュタグ）がついている単語の検索結果は全く同じ

2014年12月3日2017年11月19日

最近、形態素解析とか検索エンジンに興味があります。TwitterAPIで呼び出したつぶやき本文を、データベースに挿入して、何か意味のあるつぶやき一覧を表示させたいからです。

といっても、MySQLの機能を使って、like検索やfulltext検索もしたことがないので、まずはそこから。

今回は、JR大阪三越伊勢丹フレッシュマーケット@wjri_o_marketさんのユーザータイムライン(200件）を使わせていただき、それぞれの違いを分析してみました。

こちらのアカウントを使わせていただいたのは、各つぶやきに#（ハッシュタグ）がたくさん付いているのと、生鮮食料品で馴染みのある単語が多いためです。

なお前提として、以下のデータベーステーブルを用意しています。

create table tweet (
	id int(11) not null auto_increment primary key,
　　　　// スクリーンネーム
	tw_screen varchar(16),
　　　　// つぶやきの日時
	tw_date varchar(25),
　　　　// つぶやき本文
	tw_txt text
);

like検索とfulltext検索について

まずは用語の解説から。

like検索

WHERE句で文字列の部分一致条件など、あいまい検索に使えます。

fulltext検索

専用の MATCH AGAINST 構文を用いたSELECT句で検索する事により、マッチ率の高い順 (近似値順) にソートされたレコードセットを取得することができる

MySQLで全文検索 – FULLTEXTインデックスの基礎知識｜blog｜たたみラボ

fulltext検索の使い方

like検索はそのまま何もしなくても使えますが、fulltext検索は事前に準備が必要です。

/etc/my.cnfの設定

$sudo vi /etc/my.cnf
[mysqld]
ft_min_word_len=1

MySQLファイルの設定ファイルを開いて、fulltext検索ができる最低文字数を1文字とします。

テーブル構造の変更

alter table tweet engine MYISAM;
alter table tweet add fulltext(tw_txt);

ストレージエンジンのきりかえ（イノディービーからマイアイサム)をし、fulltext検索の追加をします。

つぶやき本文の検索について

つぶやき本文が入るtw_txtを検索すると、3つの特徴が分かりました。

1.#がついている単語は同数ヒット

select * from tweet where tw_txt like '%#JR大阪三越伊勢丹%' \G
select * from tweet where match(tw_txt) against('#JR大阪三越伊勢丹') \G

当たり前といえば、当たり前かもしれませんが、ハッシュタグが付いている単語は同数がヒットします。ただし後述しますが、fulltext検索では、”#梅田”などの4文字以下の文字列ではヒットしません。

2.#が付いていない単語は同数ヒットしない

select * from tweet where tw_txt like '%クリスマス%' \G
select * from tweet where match(tw_txt) against('クリスマス') \G

like検索では、”クリスマス”という単語をすべてヒットしますが、

fulltextはすべてがヒットしません。こちらの方は数が少ないです。

3.最低5文字以上必要！？

select * from tweet where match(tw_txt) against('鍋') \G
select * from tweet where match(tw_txt) against('伊勢丹') \G
select * from tweet where match(tw_txt) against('大阪 三越') \G
select * from tweet where match(tw_txt) against('三越伊勢丹') \G
select * from tweet where match(tw_txt) against('三越伊勢丹　鍋') \G

先に1文字以上でも検索できる設定をした割には、文字数が少ないと検索してくれません。具体的には、3行目の”大阪三越”までは、全くヒットしません。

どうやら、fulltext検索機能を使うためには、5文字以上の文字列が必要のようです。理由は分かりません。