1296997797 CakePHP でキーワードにマッチしなかったデータを取り出したいと思っています。MySQL にあまり詳しくないため、適当量のキーワードを一度取り出して、その取り出したキーワードを一つずつ何度も SQL を発行して、マッチしなかったものを変数に入れています。


添付画像にデータベースの構成の画像を入れておきました。キーワードテーブルとデータテーブルという感じです。

私の方法では一度に500件のキーワードを回したら数分経っても結果が返ってきませんでしたので、なんとか上手く1万件程度の結果を短時間で得る方法は無いでしょうか。もちろん私でも同時進行で勉強をしながら答えを探しますが、簡単であればそのものズバリの答えを教えて頂けると大変助かります。

回答の条件
  • 1人2回まで
  • 13歳以上
  • 登録:2011/02/06 22:10:00
  • 終了:2011/02/13 22:10:02

ベストアンサー

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/02/06 23:28:28

ポイント35pt

存在しないものを検索させるということは、結果的に全てのレコードを対象にするのと大差ないことなので、いっそのこと総当りにしてしまうほうがシンプルイズベストということになる場合も多く、とりあえずとなりますが下記のような方法をまずは提案します

(実際のデータの中身がわかれば、もっともっと高速化できる可能性はありますが、とりあえず処理ができないことには前に進めないと思いますので、とりあえずです。動作は確認してありますが1万件などではやってません)

SELECT T.name
FROM (
    SELECT TD.name, 
        SUM(CASE WHEN TD.name LIKE concat('%', TK.keyword, '%') THEN 1 ELSE 0 END) AS cnt
    FROM calorie_datas TD, calorie_search TK
    WHERE (TK.created BETWEEN '2011-02-05' AND '2011-02-06')
    GROUP BY TD.name
) T
WHERE T.cnt = 0
;

流れとしては下記のようになっています

(1)calorie_datasの全レコード と calorie_searchの'2011-02-05'から'2011-02-06'までを総当りでぶつけ

(2)キーワードが何件含まれているかを数え

(3)数えた結果がゼロ件のものだけを最終的に出力する

id:Sorai

回答を頂きありがとうございます。ただ、私の環境で動かすことができないので、まずは SQL を解体してひとつずつ理解して再構築して使う必要があります。それまで少し時間がかかります。

2011/02/07 15:48:20

その他の回答(1件)

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/02/06 23:28:28ここでベストアンサー

ポイント35pt

存在しないものを検索させるということは、結果的に全てのレコードを対象にするのと大差ないことなので、いっそのこと総当りにしてしまうほうがシンプルイズベストということになる場合も多く、とりあえずとなりますが下記のような方法をまずは提案します

(実際のデータの中身がわかれば、もっともっと高速化できる可能性はありますが、とりあえず処理ができないことには前に進めないと思いますので、とりあえずです。動作は確認してありますが1万件などではやってません)

SELECT T.name
FROM (
    SELECT TD.name, 
        SUM(CASE WHEN TD.name LIKE concat('%', TK.keyword, '%') THEN 1 ELSE 0 END) AS cnt
    FROM calorie_datas TD, calorie_search TK
    WHERE (TK.created BETWEEN '2011-02-05' AND '2011-02-06')
    GROUP BY TD.name
) T
WHERE T.cnt = 0
;

流れとしては下記のようになっています

(1)calorie_datasの全レコード と calorie_searchの'2011-02-05'から'2011-02-06'までを総当りでぶつけ

(2)キーワードが何件含まれているかを数え

(3)数えた結果がゼロ件のものだけを最終的に出力する

id:Sorai

回答を頂きありがとうございます。ただ、私の環境で動かすことができないので、まずは SQL を解体してひとつずつ理解して再構築して使う必要があります。それまで少し時間がかかります。

2011/02/07 15:48:20
id:taroe No.2

taroe回答回数1099ベストアンサー獲得回数1322011/02/07 06:53:04

ポイント35pt

>SELECT name FROM calorie_datas WHERE name NOT LIKE BINARY '%' . (SELECT keyword FROM calorie_search WHERE created > 2011-02-05 AND created < 2011-02-06 ORDER BY keyword DESC) . '%'

このように書くのなら、キーワード分素直にループしたほうが早いと思います。

中間一致のLIKE検索はインデックスも効きませんので、calorie_datasを全件ループしたほうが

良いと思います。calorie_search を取り出したキーワードはPHPの変数か配列に抱え込んでおく。

で、calorie_datasのnameとkeyword をPHPでパターンマッチをかけるです。

これでもそれなりの時間はかかると思います。

もし使われているDBで、全文検索機能がサポートされてるのならそれを使うのも1つの方法です。

1.calorie_datas 1レコード読む

2.keyword をPHPでパターンマッチ(keyword 分だけループ)

です。

もしくは、calorie_datasに登録する時点で、nameを単語に分解しておいて、別フィールドに登録

そのフィールドに普通に検索をかけるのなら、インデックスが効きますので

SQLでも十分許容範囲の応答速度になるはずです。

id:Sorai

keyword 分だけループすると1日分だけで10秒かかりますね。月毎に出したいので5分から15分ってところでしょうか。難しいですね。

2011/02/07 15:35:44
  • id:windofjuly
    うぃんど 2011/02/06 22:28:07
    >私の方法では一度に500件のキーワードを回したら数分経っても結果が返ってきません
     
    その方法とSQL(テーブルのフィールド構成も必要になってくる場合もあります)を書いてもらえればネック部分を示すのも簡単になりますが・・・
  • id:Sorai
    SELECT name FROM calorie_datas WHERE name NOT LIKE BINARY '%' . (SELECT keyword FROM calorie_search WHERE created > 2011-02-05 AND created < 2011-02-06 ORDER BY keyword DESC) . '%'

    これでできるのか少し期待したけどダメでした…。
  • id:sayo213sayo
    質問内容と直接関係しないことで失礼します。
    最近コメント荒らしが増えています。
    質問中はコメント欄をクローズしておいた方が安全です。
  • id:tdoi
    一度に検索するキーワードは500くらいまでと想定してよいのでしょうか?
  • id:Sorai
    > sayo213sayo さん

    コメント欄を空けておかないとコメント欄を使えないかと思いました。それでもクローズしておくことが一般的なのでしょうか?

    > tdoi さん

    一度に検索するキーワードは10000件を想定しています。現在300件程度で10秒以上かかっています。
  • id:taroe
    >keyword 分だけループすると1日分だけで10秒かかりますね。月毎に出したいので5分から15分ってところでしょうか。難しいですね

    処理時間が妥当かどうかは言及をさけますが、
    この手の処理は、リアルタイムには通常行われません。
    バッチなどで定期的に実行してデータを作成しておく場合が多いです。

    どういうものかわからないので、はずしていたらごめんなさい。

  • id:Sorai
    なるほど…。スケジューリングして定期的に少しずつデータを溜めていくのが一般的なのでしょうか。物凄い勉強になります!ただキーワード検索の結果はリアルタイムに変わっていくので正確な値を出すにはどうしても難しそうですね。
  • id:windofjuly
    うぃんど 2011/02/08 01:56:30
    【1】リアルタイムで行うには非現実的な物理的マシンパワーが要求されそうです
    よくある対処方法としてxx時xx分現在という形で表示するといった手法があります
    「月毎に出したいので5分から15分」であるならば、余裕をもって20分に一回のタイミングで集計を行って結果収納用テーブルに保存させるといった具合です
     
    上記に加えて、SQLの高速化もあわせて行い、20分に一回ではなく15分に一回や10分に一回などと更新タイミングを早くしてことを考えると良いでしょう
    まずは「定期的に集計を行う」という形で、とにかく動くものをつくることを考えてみることを奨めます
     
    【2】SQL実行の高速化について
    回答1のコードが動かないという理由がわかりませんので対処方法が浮かんでないのですが、さらなる高速化手段としてはcalorie_searchテーブルの最適化(createdフィールドにインデックスをつけておく、重複しているレコードを除くなど)を行ってから、回答1のコードを動かすという方法などがあります
     
    以上、とりあえず追記です
  • id:sayo213sayo
    >コメント欄を空けておかないとコメント欄を使えないかと思いました。それでもクローズしておくことが一般的なのでしょうか?
     
    クローズしておかないと荒らされますよ。

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

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

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

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