複数ﾌｨｰﾙﾄﾞ＆別ﾃｰﾌﾞﾙが絡む検索(MySQL)について教えて下さい記事の投稿ができ､各記事に対してﾀｸﾞ付けができるｻｲﾄを検討しています｡その中でﾜｰﾄﾞ検索を…

人力検索はてな

ﾓﾊﾞｲﾙ版を表示しています｡PC版はこちら

複数ﾌｨｰﾙﾄﾞ＆別ﾃｰﾌﾞﾙが絡む検索(MySQL)について教えて下さい

記事の投稿ができ､各記事に対してﾀｸﾞ付けができるｻｲﾄを検討しています｡
その中でﾜｰﾄﾞ検索を行いたいと考えています｡
例を示します｡

[postsﾃｰﾌﾞﾙ]
pid title city txt
1 映画東京のびたがついに大活躍
2 映画東京ｽﾎﾟｰﾂ系物語
3 演劇大阪お笑い系
･･･

[tagsﾃｰﾌﾞﾙ]
tid pid tag
1 1 どらえもん
2 1 面白い
3 2 青春
･･･

【検索の仕様】
･検索対象ﾌｨｰﾙﾄﾞは以下
postsﾃｰﾌﾞﾙのtitle, city, txt および tagsﾃｰﾌﾞﾙのtag
･複数語検索に対応(空白区切りのAND検索)

例えば､
検索窓に｢のびたどらえもん｣と入れるとpid1がﾏｯﾁします｡
(検索語がpostsのtxtとtagsのtagにlikeﾏｯﾁ)
｢どらえ面白｣と入れるとpid1がﾏｯﾁします｡
(検索語がtagsのtagにlikeﾏｯﾁ)
同様に｢映画東京ｽﾎﾟｰﾂ｣だとpid2がlikeﾏｯﾁします｡

環境はMySQL､PHPになります｡
ここ数日､どのようなSQL文を組んだら良いのか悩んでいます｡
ｱﾄﾞﾊﾞｲｽ頂ければ嬉しいです｡

(できれば､補足情報としてtagにﾏｯﾁしたどうかが分かるﾌﾗｸﾞ情報まで得られればﾍﾞｽﾄです｡)

●質問者: puuyan
●ｶﾃｺﾞﾘ:ｺﾝﾋﾟｭｰﾀｳｪﾌﾞ制作
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

▽1 ● きゃづみぃ
●100ﾎﾟｲﾝﾄ

検索に使用する単語は一つにしたほうがいいですね｡

たとえば｢映画東京ｽﾎﾟｰﾂ｣の場合は｢映画｣､｢東京｣､｢ｽﾎﾟｰﾂ｣の3つにわけて 3回ﾙｰﾌﾟして取得する｡

上記のｷｰﾜｰﾄﾞで
[postsﾃｰﾌﾞﾙ]
titleとcityとtxtに対してそれぞれ likeでORで pidを取得する｡

[tagsﾃｰﾌﾞﾙ]
こちらは tagで like 検索して pidを取得する｡

これを単語数繰り返してどの単語でも取得できた pidを検索結果とする｡

こんな感じでできますか？

puuyanさんのｺﾒﾝﾄ
ありがとうございます｡よろしければもう少しお聞きしたいです｡ (1)保持配列ご回答頂いた仕組みは理解できます｡ありがとうございます｡私が組むとしたら以下のような形になると思います｡この場合､ﾙｰﾌﾟ内ではpidのみを取得する形が良いでしょうか？その場合はﾙｰﾌﾟ終了後に､pidをｷｰにして再度DB検索(SQLのINを使う？)を行う必要が出てくると思いますが､仕方ないでしょうか｡もしくは思い切ってﾙｰﾌﾟ内で全利用ﾌｨｰﾙﾄﾞを取得してしまうのもありだと思いますが｡このあたりは件数見合いで変わってくる気がしますが､ｱﾄﾞﾊﾞｲｽを頂ければ嬉しいです｡ (最終的に必要なのはpidのみではなくpostsﾃｰﾌﾞﾙの当該ﾚｺｰﾄﾞﾌｨｰﾙﾄﾞになります｡) $kekka = array(); foreach ($words as $word) { $res1 = postsﾃｰﾌﾞﾙ(title,city,txt)から検索した結果配列(pidのみ?/全利用ﾌｨｰﾙﾄﾞ?) $res2 = tagsﾃｰﾌﾞﾙ(tag)から検索した結果配列(pidのみ?/全利用ﾌｨｰﾙﾄﾞ?) $res = res1とres2のﾏｰｼﾞ(array_unique等を使って重複削除) if (!$kekka) { $kekka = $res; } else { $kekka = array_intersect($kekka, $res); // 共通部分を求める } } ･$kekkaに最終結果が入る(pidのみ?/全利用ﾌｨｰﾙﾄﾞ?) ･ﾙｰﾌﾟ内でpidのみを取得している場合は､pidをｷｰに再度DB検索して当該ﾚｺｰﾄﾞの必要ﾌｨｰﾙﾄﾞを得る｡(SQLのIN演算子を利用？) (2)SQL発行回数今回の仕組みですと｢映画東京ｽﾎﾟｰﾂ｣の場合は最低6回のSQLが発行されます｡ (ﾙｰﾌﾟ3回､ﾙｰﾌﾟ内で2回(postsﾃｰﾌﾞﾙおよびtagsﾃｰﾌﾞﾙを対象)) 出来ればSQL発行回数を抑えたいのですが､妙案はありませんでしょうか｡しかしﾄﾘｯｷｰにSQLを使用するのは避けたいと思いますので､通常適用される範囲で良い案があれば教えて頂けると嬉しいです｡

きゃづみぃさんのｺﾒﾝﾄ
＞この場合､ﾙｰﾌﾟ内ではpidのみを取得する形が良いでしょうか？そうですね｡ﾒﾓﾘとの兼ね合いですが､件数が少ないようでしたら全部取得したほうがﾗｸでしょう｡ま､これぐらいの項目数ならば全部取得してもいいと思います｡抽出内容が同じものは UNION で SQLを連結させれば発行回数自体は減らせます｡

puuyanさんのｺﾒﾝﾄ
ありがとうございます｡ PHP側での力技部分が多くなりますが､確かにこの仕組みを採用すれば今回の検索は実現できそうですね｡候補の一つとして考えます｡

▽2 ● taroe
●100ﾎﾟｲﾝﾄ

ﾍﾞｽﾄｱﾝｻｰ

今回の場合は､
[postsﾃｰﾌﾞﾙ]と[tagsﾃｰﾌﾞﾙ]をJOINして
これをSQLで検索する方向で考えてみるべき

たぶん､SQL1回でOKだと思うけど｡
ﾄﾘｯｷｰでなくて普通に使われる手法｡

JOIN自体がﾄﾘｯｷｰなら､方法はﾌﾟﾛｸﾞﾗﾑでがんばるしかない｡

＞今回の仕組みですと｢映画東京ｽﾎﾟｰﾂ｣の場合は最低6回のSQLが発行されます｡
＞(ﾙｰﾌﾟ3回､ﾙｰﾌﾟ内で2回(postsﾃｰﾌﾞﾙおよびtagsﾃｰﾌﾞﾙを対象))
＞出来ればSQL発行回数を抑えたいのですが､妙案はありませんでしょうか｡
＞しかしﾄﾘｯｷｰにSQLを使用するのは避けたいと思いますので､通常適用される範囲で良い案があれば教えて頂けると嬉しいです｡

[postsﾃｰﾌﾞﾙ]と[tagsﾃｰﾌﾞﾙ]をJOINして
SQLで検索すれば､SQL発行回数は減りますが･･･｡

JOINは普通の方法ですし､そんなに遅いわけでもなくて
ﾌﾟﾛｸﾞﾗﾑでﾙｰﾌﾟしてﾏｯﾁさせるより早いこともあります｡

これ､ﾃﾞｰﾀ件数とかにもよるんですね｡
ﾃﾞｰﾀ件数が多くなるほど､SQLで力尽くJOINというのは遅くなる傾向にあり
ﾌﾟﾛｸﾞﾗﾑで力尽くの方がかえって早くなるということがあります｡

SQLの発行回数よりも
実際の処理時間

SQL3回と
SQL6回でも

6回の方が早いことも多々ありますよ｡

puuyanさんのｺﾒﾝﾄ
ご回答ありがとうございます｡＞[postsﾃｰﾌﾞﾙ]と[tagsﾃｰﾌﾞﾙ]をJOINして＞これをSQLで検索する方向で考えてみるべきはい｡私もjoinで考え始めました｡しかし､私のSQLｽｷﾙでは上手く出来ず､ご質問させて頂きました｡ joinで詰まっている箇所は以下になります｡ [SQL例] select p.* from posts as p left join tags as t on p.pid=t.pid where (p.title like '%のびた%' or p.city like '%のびた%' or p.txt like '%のびた%' or t.tag like '%のびた%') and (p.title like '%どらえもん%' or p.city like '%どらえもん%' or p.txt like '%どらえもん%' or t.tag like '%どらえもん%') 当初､上記のようなSQLを考えました｡例のように｢のびたどらえもん｣の検索では上手く検索できます｡ですが､｢どらえ面白｣の場合は検索できません｡('どらえ'と'面白'はいずれもtagsﾃｰﾌﾞﾙのtagに存在します｡) tagsﾃｰﾌﾞﾙがﾛｸﾞ型？(ｽﾄｯｸ型というのでしょうか？､同一のpidが複数存在します｡)になっているため､上記のようなSQLではjoinしても検索できないということまでは分かりました｡じゃあどうしたら良いのか､私のSQLｽｷﾙでは正直良く分かりません｡先に進めず､ここ数日悩んでいます｡ｱﾄﾞﾊﾞｲｽを頂ければ嬉しいです｡＞SQLの発行回数よりも＞実際の処理時間＞SQL3回と＞SQL6回でも＞6回の方が早いことも多々ありますよ｡おっしゃる通りなのだと思います｡失礼しました｡

taroeさんのｺﾒﾝﾄ
■方法1 一番簡単なのは､ 1記事につけれるﾀｸﾞの数に制限をつけて [tagsﾃｰﾌﾞﾙ]を pid tag名1 tag名2 のような構造にして､ LEFT JIONでなくて1対1のJOINをすれば簡単になると思うが･･ﾃｰﾌﾞﾙ構造も2ﾃｰﾌﾞﾙでなくて 1.記事 2.pid id 対応表 3.ﾀｸﾞﾏｽﾀ id tag名の用に分けてみても｡現状､ﾀｸﾞidって存在する意味さえなさそうだけど｡ﾜｰﾄﾞ検索で･記事ﾀｲﾄﾙ関係とﾀｸﾞとを同時に扱う･AND条件この2つが難しくしてると思う｡ SQLのIN句 IN ﾌｨｰﾙﾄﾞ名 (SECECL * FROM TABLE WHERE ***) または IN ﾌｨｰﾙﾄﾞ名 (値1,値2,値3 ***) みたいな物も使えると思う｡あとlike検索は元々遅いのでこの方式自体がどうかなというところもあると思う｡

puuyanさんのｺﾒﾝﾄ
ありがとうございます｡＞1記事につけれるﾀｸﾞの数に制限をつけて＞[tagsﾃｰﾌﾞﾙ]を＞pid tag名1 tag名2 ＞のような構造にして､うｰん､こちらの方法は余りｽﾏｰﾄではありませんね｡＞ﾃｰﾌﾞﾙ構造も2ﾃｰﾌﾞﾙでなくて＞1.記事＞2.pid id 対応表＞3.ﾀｸﾞﾏｽﾀ id tag名＞の用に分けてみても｡ありがとうございます｡この3ﾃｰﾌﾞﾙ構成にした場合は､現在ﾈｯｸになっている部分は解決するのでしょうか？(結論として1対1のjoinになる？) 頭がまわらず､この構成にする利点が良く掴めておりません｡＞ﾜｰﾄﾞ検索で＞･記事ﾀｲﾄﾙ関係とﾀｸﾞとを同時に扱う＞･AND条件＞この2つが難しくしてると思う｡おっしゃる通りで悩んでいます｡＞あとlike検索は元々遅いので＞この方式自体がどうかなというところもあると思う｡他にどんな方式があるのでしょうか？ﾋﾝﾄや障りだけでも教えて頂けると助かります｡