◆やりたいこと
・tableというテーブルのcolumnというカラム(CHAR(10))に、半角英字10文字で構成される文字列が格納されている(ユニークキーあり)
・このレコードは、理論的には、1年あたり最大100万件のペースで増え続ける
・tableのレコード全件のcolumnに対して、半角英字(文字数は問わない)で検索をかけたい
・ただし、完全一致だけでなく、「似ている」文字列に重みづけをして、上位10件程度表示したい
・似ているかどうかの判断に詳細な仕様の指定はなく、ある程度妥協してOK
・操作者は1名~数名なので、負荷についてはある程度許容可能だが、
1日に何度も行う手続きで毎回通るところなので、処理時間は極力短くしたい
例えば以下のレコードに対し「abcdefghij」という文字列で検索すると、4→3→2→1くらいの順序で出ると良いです。
1:zzzzzzzzzz
2:adefghijzz
3:aacdefghij
4:abcdefghij
◆案・試してみたこと
①まず全件SELECTして、PHP側でsimilar_text関数やlevenstein関数を使って重みづけ・ソート
→件数によってはSELECT時にPHPがメモリオーバーを起こす恐れがあるため、
何らかの条件である程度絞ってSELECTするという妥協もあり
②完全一致でSELECT、8文字程度の前方一致でSELECT…と
少しずつ条件を緩和しつつ何度かSELECTし、10件以上抽出できた時点で終了
→PHPから何度もSQL実行するのはオーバーヘッドが大きくなるためできれば避けたいが、
遅い複雑なSQLよりはマシかもしれない…
③MySQLのストアドプロシージャでPHPのlevenstein関数のようなものを作る
→他の方が試していたので実は詳細を把握していないのですが、
1万件から検索するのに15秒ほどかかっていました。
④abcd→「a ab bc cd d」のように分割した文字列を格納するフィールドを別途用意して
FULLTEXTインデックスを張り、MATCH~AGAINST構文でSELECT
→インデックスの制約に引っかかってうまく検索できない
(50%以上のレコードに含まれている単語は無視される)
MySQLのソースを書き換えれば動くようだが、同じサーバで稼働中のサイトもあり、極力避けたい
⑤2文字程度までの不一致は許可するとして、検索する文字列が「abcdefghij」とすると、
「%cdefghij%」「%a%defgihj%」「%ab%efghij%」…という文字列を生成してLIKE検索する
→取得した中での重みづけが必要なら考慮する必要がある。また、部分一致でのSELECTだと速度面が不安
今のところは、①と⑤の合わせ技が落としどころかな…と思っています。
が、上記の通り、部分一致のSELECTは速度面に不安が残ります。
ちょっとした改善案から全然別のアイデアまで、何か思いつくことがあればお願いいたします!
回答1件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2016/02/24 09:09