質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

Q&A

解決済

1回答

5490閲覧

MySQLかPHPで、似ている文字列を順序付けして取得したい

takushi168

総合スコア228

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

PHP

PHPは、Webサイト構築に特化して開発されたプログラミング言語です。大きな特徴のひとつは、HTMLに直接プログラムを埋め込むことができるという点です。PHPを用いることで、HTMLを動的コンテンツとして出力できます。HTMLがそのままブラウザに表示されるのに対し、PHPプログラムはサーバ側で実行された結果がブラウザに表示されるため、PHPスクリプトは「サーバサイドスクリプト」と呼ばれています。

1グッド

0クリップ

投稿2016/02/24 01:19

編集2016/02/24 01:20

◆やりたいこと

・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は速度面に不安が残ります。

ちょっとした改善案から全然別のアイデアまで、何か思いつくことがあればお願いいたします!

nikuatsu👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答1

0

ベストアンサー

どのタイミングでなら負荷をかけて良いのかを明確にすると良いと思います。
24時間ずっと検索されることを想定するのか、業務時間内で良いのかなど。
もし、ある時間で負荷をかけて良いのであれば、文字に対する類似度を計算するバッチを走らせて、類似度テーブルを作成する(最大x件までと制限すればテーブルのサイズもそこそこで収まる)

文字が登録されると即座に反映しなければならないということであれば、最初だけ上記のようなバッチを走らせ、類似度テーブルを作成、その後は登録の際に類似度を検索して、既存の類似度テーブルをアップデートする

ある文字の類似度検索をする場合、文字検索後に、そのレコードのIDから類似度テーブルを検索して、類似度順にソートして表示する。

投稿2016/02/24 02:33

t_obara

総合スコア5488

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

takushi168

2016/02/24 09:09

ご回答ありがとうございます。大変参考になりました! もし負荷をかけるとすれば深夜が望ましいですが、 リアルタイム反映でないと厳しいので、登録時アップデートが良いですね。 …ですが、色々と速度検証などを行った結果、 1. 一定の条件で1000件ほどSELECT 2. 1の結果に対してlevenstein関数を使用した結果をソートして上位10件を返す ということになりました。(工数の都合もあり…。) 今後、改修を行う際などには検討していこうという話になりました。ありがとうございます!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問