« Catalyst::Plugin::Captchaリリース | メイン | 仕事終了& YAPC »

Tagの検索にMySQLの全文検索を使う

Tags with MySQL fulltextを参考にして試してみた。

Femoの中で、タグの絞り込み機能を実装したのに続いて、「完了」や「finish」と言ったタグがついている場合表示しないというオプションを考えている。
そうなってくると、SQLをどう書けばいいのか、また複雑なSQLを構築したときにパフォーマンスは大丈夫なのかと心配。そこで、上記のURLを参考にしながらMySQLの全文検索に注目。

create table ft_tags(
id int unsigned not null auto_increment primary key,
tags text,
unpack text,
fulltext (unpack)
)

と言うテーブルを作成。
ここに、

my @tags = (
q/task femo/,
q/femo mail task/,
q/task femo/,
q/task plagger/,
q/task 2006-03-27 "perl monger"/,
q/task 終了/,
q/feedback femo task dev/,
q/task feeder dev/,
q/femo task dev/
);

のデータを入れる。

MySQLの全文検索のテキストの切り分けは半角スペース。"perl monger"のようなタグには対応できない。またcase insensibleなので、Tag検索に使えない。
なので、「MySQLのFull Text Searchを日本語に使う実験」思い出して、Chasenで形態素解析ではなく、Text::Tagsを用いて文字列を分割して、unpack & 16進数に変換して保存してみる

保存するコードは

my $sth = $dbh->prepare("insert into ft_tags set tags=?, unpack=?");
for my $tags (@tags){
    my @unpacks;
    $tags = Encode::decode_utf8($tags);
    for my $tag (Text::Tags::Parser->new->parse_tags($tags)) {
        push @unpacks, join 'x', map { sprintf("%02x",$_) } 
            unpack("U*", $tag);
    }
    $sth->execute(
        join(' ', $tags),
        join(' ', @unpacks)
    );
}


データはこうなる。unpackした数値は「x」で繋ぐ。

mysql> select * from ft_tags ;
+----+-------------------------------+----------------------------------------------------------------------------+
| id | tags                          | unpack                                                                     |
+----+-------------------------------+----------------------------------------------------------------------------+
|  1 | task femo                     | 74x61x73x6b 66x65x6dx6f                                                    |
|  2 | femo mail task                | 66x65x6dx6f 6dx61x69x6c 74x61x73x6b                                        |
|  3 | task femo                     | 74x61x73x6b 66x65x6dx6f                                                    |
|  4 | task plagger                  | 74x61x73x6b 70x6cx61x67x67x65x72                                           |
|  5 | task 2006-03-27 "perl monger" | 74x61x73x6b 32x30x30x36x2dx30x33x2dx32x37 70x65x72x6cx20x6dx6fx6ex67x65x72 |
|  6 | task 終了                   | 74x61x73x6b 7d42x4e86                                                      |
|  7 | feedback femo task dev        | 66x65x65x64x62x61x63x6b 66x65x6dx6f 74x61x73x6b 64x65x76                   |
以下省略


「task」と「femo」を含んで「終了」を含まない、という条件で検索したいとき

select * from ft_tags where match(unpack) against('+74x61x73x6b +66x65x6dx6f -7d42x4e86' in boolean mode);

というクエリーを発行すればOK。検索文字列ももちろんunpackする。
上のクエリ−の結果は、@ID=qw/1 2 3 7 9/で希望通り。

これなら柔軟にタグの検索ができるはず。

MySQLの全文検索は検索の速度に不満はないだろうけど、indexの作成速度が少し心配。
Sennaのような感じでMySQLの全文検索より効率の良いTag検索エンジンがあればいいのになぁ。

トラックバック

この一覧は、次のエントリーを参照しています: Tagの検索にMySQLの全文検索を使う:

» Tagの検索をSenna+MySQLで from YappoLogs
kazeburoさんの所でfulltext使ってtagを実装するネタがアツめなのでちょろっと書いてみる。 SennaのMySQL 2indパッチを使うと、... [詳しくはこちら]

» [検索]タグの検索 from グニャラくんのグニャグニャ備忘録@はてな
Tagの検索にMySQLの全文検索を使う https://blog.nomadscafe.jp/archives/000643.html SEN_IND... [詳しくはこちら]

» [Senna]Tagの検索にMySQLの全文検索とSennaを使う from グニャラくんのグニャグニャ備忘録@はてな
revision 105で対応。 MySQLパッチのsen_index_create関数の第3引数に SEN_INDEX_DELIMITED を入れると吉... [詳しくはこちら]

» [Perl][CDBI] 確かにtagのAND検索をするモジュールが必要だ。 from 浅倉卓司@blog風味?
 ついさっきはてなブックマークだと、入力時に一旦 parse して分解してしまって、正規化した tag テーブルにひとつのタグでひとつのレコードという形で... [詳しくはこちら]

コメント

タグ用のテーブルを作って、記事テーブルとn*mのリレーションを張る って感じじゃないんですね。ただ、登録時に記事のIDが分からないといけないので、それをどうするかという問題もありますが。
そういえばMySQLって正規表現がつかえませんでしたっけ?うまく使えば何とかできませんか?

> たかみちえさま
通常はタグのテーブルを作成する形だと思います。ただ、複雑な条件のタグ検索をしたいときにSQLが難しくなりそうです。
MySQLだと特に遅くなってしまいそうです。
正規表現でもいいのですが、たぶん速度が。。

コメントを投稿