複数フィールド&別テーブルが絡む検索(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にマッチしたどうかが分かるフラグ情報まで得られればベストです。)

回答の条件
  • 1人5回まで
  • 13歳以上
  • 登録:2012/04/18 11:03:52
  • 終了:2012/04/25 11:05:06

ベストアンサー

id:taroe No.2

taroe回答回数1099ベストアンサー獲得回数1322012/04/20 00:05:54

ポイント100pt

今回の場合は、
[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回の方が早いことも多々ありますよ。

他1件のコメントを見る
id: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検索は元々遅いので
この方式自体がどうかなというところもあると思う。

2012/04/20 21:11:17
id:puuyan

ありがとうございます。

>1記事につけれるタグの数に制限をつけて
>[tagsテーブル]を
>pid tag名1 tag名2
>のような構造にして、

うーん、こちらの方法は余りスマートではありませんね。

>テーブル構造も2テーブルでなくて
>1.記事
>2.pid id 対応表
>3.タグマスタ id tag名
>の用に分けてみても。

ありがとうございます。
この3テーブル構成にした場合は、現在ネックになっている部分は解決するのでしょうか?(結論として1対1のjoinになる?)
頭がまわらず、この構成にする利点が良く掴めておりません。

>ワード検索で
>・記事タイトル関係とタグとを同時に扱う
>・AND条件
>この2つが難しくしてると思う。

おっしゃる通りで悩んでいます。

>あとlike検索は元々遅いので
>この方式自体がどうかなというところもあると思う。

他にどんな方式があるのでしょうか?
ヒントや障りだけでも教えて頂けると助かります。

2012/04/21 11:46:02

その他の回答(1件)

id:taknt No.1

きゃづみぃ回答回数13539ベストアンサー獲得回数11982012/04/18 11:15:10

ポイント100pt

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

たとえば 「映画 東京 スポーツ」の場合は 「映画」、「東京」、「スポーツ」の3つにわけて 3回ループして 取得する。

上記のキーワードで
[postsテーブル]
titleとcityとtxtに 対して それぞれ likeでORで pidを取得する。


[tagsテーブル]
こちらは tagで like 検索して pidを取得する。

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


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

他1件のコメントを見る
id:taknt

>この場合、ループ内ではpidのみを取得する形が良いでしょうか?

そうですね。メモリとの兼ね合いですが、件数が少ないようでしたら 全部取得したほうが ラクでしょう。

ま、これぐらいの項目数ならば 全部 取得してもいいと思います。
抽出内容が 同じものは UNION で SQLを連結させれば 発行回数自体は 減らせます。

2012/04/19 19:26:41
id:puuyan

ありがとうございます。

PHP側での力技部分が多くなりますが、確かにこの仕組みを採用すれば今回の検索は実現できそうですね。
候補の一つとして考えます。

2012/04/20 08:37:53
id:taroe No.2

taroe回答回数1099ベストアンサー獲得回数1322012/04/20 00:05:54ここでベストアンサー

ポイント100pt

今回の場合は、
[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回の方が早いことも多々ありますよ。

他1件のコメントを見る
id: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検索は元々遅いので
この方式自体がどうかなというところもあると思う。

2012/04/20 21:11:17
id:puuyan

ありがとうございます。

>1記事につけれるタグの数に制限をつけて
>[tagsテーブル]を
>pid tag名1 tag名2
>のような構造にして、

うーん、こちらの方法は余りスマートではありませんね。

>テーブル構造も2テーブルでなくて
>1.記事
>2.pid id 対応表
>3.タグマスタ id tag名
>の用に分けてみても。

ありがとうございます。
この3テーブル構成にした場合は、現在ネックになっている部分は解決するのでしょうか?(結論として1対1のjoinになる?)
頭がまわらず、この構成にする利点が良く掴めておりません。

>ワード検索で
>・記事タイトル関係とタグとを同時に扱う
>・AND条件
>この2つが難しくしてると思う。

おっしゃる通りで悩んでいます。

>あとlike検索は元々遅いので
>この方式自体がどうかなというところもあると思う。

他にどんな方式があるのでしょうか?
ヒントや障りだけでも教えて頂けると助かります。

2012/04/21 11:46:02
  • id:puuyan
    結局、以下のようなSQLで自己解決しました。
    何かの参考までに書き込んでおきます。

    select p.*,
    sum(case when (t.tag like '%どらえ%' or t.tag like '%面白%') then 1 else 0 end) as tag_flag
    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
    p.pid in (select pid from tags where tag like '%どらえ%'))
    and
    (p.title like '%面白%' or p.city like '%面白%' or p.txt like '%面白%' or
    p.pid in (select pid from tags where tag like '%面白%'))
    group by p.pid

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません