タグ: 転置インデックス を抽出しています。
Total: 1

FULLTEXTインデックス(MyISAM)から転置インデックス(InnoDB)へ

ブログ | 2010/11/28 23:04
全文検索を実装するときに、転置インデックス的なやり方(そんな名前がついてるとか知らなかったけれど)は検討したのだけれど、どんでもないレコード数になるから、扱いづらくて遅いんじゃないかと思ってました。

で、実験中。

table
CREATE TABLE IF NOT EXISTS `inno_node_index` (
 `ngram` varchar(3) NOT NULL,
 `nid` int(11) NOT NULL,
 PRIMARY KEY (`ngram`,`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
nidは、65万件くらい。
1nidに対して、ngram化した文字が1000あったとして、レコード数は6億レコードとかになる。

insertは、10万nidで15分くらいだったから、許容範囲だろうか。

検索クエリー
SELECT SQL_NO_CACHE nid FROM `inno_node_index`
WHERE ngram IN ('差出箱','出箱7','箱7号')
GROUP BY nid
HAVING COUNT(nid) = 3;

FULLTEXTの場合は、こんな感じだったもの。
SELECT SQL_NO_CACHE nid FROM `new_node_search`
WHERE match(ngram) against ('+差出箱 +出箱7 +箱7号'  in boolean mode);

さあ、どんな結果がでるのだろう。
ただいま、データinsert中。

MyISAMで更新の多いFULLTEXTインデックスは、すぐにオーバーヘッドが発生するし、修復もハイコストだし、なんとかしたいというのが動機。InnoDB化がきっかけ。
▼追記 2010/11/29 01:10
6億レコードもなかった。70,295,304レコード。(InnoDBはレコード数をカウントするのが遅いことを知る)

では、検索スピード比較(3文字ngram)
数日前のポストマップのインデックスからです。
「差出箱7号」
InnoDB転置: (2,622 合計, クエリの実行時間 1.0055 秒)
FULLTEXT: (2,624 合計, クエリの実行時間 0.0731 秒)

「差出箱5号」
InnoDB転置: (87 合計, クエリの実行時間 1.0579 秒)
FULLTEXT: (87 合計, クエリの実行時間 0.0633 秒)

「kuwa」
InnoDB転置: (1,369 合計, クエリの実行時間 0.0236 秒)
FULLTEXT: (1,369 合計, クエリの実行時間 0.1547 秒)

「たぬき」
InnoDB転置: (37 合計, クエリの実行時間 0.0007 秒)
FULLTEXT: (37 合計, クエリの実行時間 0.0222 秒)

「愛」
InnoDB転置: (43,252 合計, クエリの実行時間 0.0014 秒)
FULLTEXT: (43,257 合計, クエリの実行時間 0.0558 秒)

「東京都港区赤坂」
InnoDB転置: (4 合計, クエリの実行時間 0.0962 秒)
FULLTEXT: (4 合計, クエリの実行時間 0.0048 秒)

「万博」
InnoDB転置: (83 合計, クエリの実行時間 0.0029 秒)
FULLTEXT: (83 合計, クエリの実行時間 0.0514 秒)

「白木屋」
InnoDB転置: (29 合計, クエリの実行時間 0.0187 秒)
FULLTEXT: (29 合計, クエリの実行時間 0.0267 秒)

「傾いている」
InnoDB転置: (37 合計, クエリの実行時間 0.1045 秒)
FULLTEXT: (37 合計, クエリの実行時間 0.1354 秒)

「そんなことじゃないかと思っていました。」
InnoDB転置: (1 合計, クエリの実行時間 0.5424 秒)
FULLTEXT: (1 合計, クエリの実行時間 0.1139 秒)
スピードに関しては、十分という気がする。3文字までなら転置の方が速くて、それ以上はFULLTEXTという結果かな。

データ容量に関しては、
InnoDB転置: 3.8G
FULLTEXT: 1.6G
倍くらいなら、気にならない。
▼追記 2010/12/6 01:54
tableのindexだけれど、
PRIMARY KEY (`nid`,`ngram`)
こうしておかないと、nidで検索したときにインデックスが使用されない。順番大事。
http://dev.mysql.com/doc/refman/5.1/ja/multiple-...

検索を更新するときに、nidで検索して削除しなければならないから。
▼追記 2010/12/6 13:19
これはとんでもない勘違いだ。検索でindexが使用できなくなるじゃないか。
CREATE TABLE IF NOT EXISTS `inno_node_index` (
 `ngram` varchar(3) NOT NULL,
 `nid` int(11) NOT NULL,
 PRIMARY KEY (`ngram`,`nid`),
 KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
nidに追加でindexをつけるしかなさそうだ。
前 | 1 | 次

Rottel内コンテンツ

ユーザー一覧

Rottelとは?
利用規約
開発飲料
利用者の声
ヘルプ
close