MySQLの特定クエリが非常に遅く(10秒以上かかることがある)、最適化しようと思っています。

MySQL5.0.45

PHPから呼び出しています。
テーブル my_site 約1万データ
テーブル my_exclude 約1万データ

LEFT JOINが原因だと思いますが、良い方法はないでしょうか。
select my_site.id as id, my_site.url as url, my_site.name as name from my_site LEFT JOIN my_exclude ON my_site.url=my_exclude.url && my_exclude.usrid=10000 where my_site.usrid!=10000 && my_exclude.url IS NULL order by rand() limit 2;

やりたい事は、
「my_siteからランダムで2行URLを抜き出す」です。
そのときに、「my_excludeにはユーザーごと(usrid)に固有の除外URLを複数指定でき、対象URLからは省く」必要があります。

これをLEFT JOINで解決していますが、他の早い方法に書き換えたいと思います。
PHP側での解決でも構いませんが、アドバイスどうぞよろしくお願いいたします。

回答の条件
  • URL必須
  • 1人10回まで
  • 登録:2010/01/10 14:45:55
  • 終了:2010/01/10 18:05:23

回答(2件)

id:km1967 No.1

km1967回答回数541ベストアンサー獲得回数402010/01/10 14:53:51

ポイント18pt

まずはお願いなのですが、

テーブルの構造やインデックスキーの張り方など

何も判らないと原因の特定は難しくなりますので、必須だとご記憶ください。

不適当な回答が集まっても混乱をきたしますから、私への返信欄か、

あるいは、コメント欄を開く設定に変更してコメント欄)に、

書き加えておくことを勧めます。

http://q.hatena.ne.jp/

id:kablog

ご指摘ありがとうございます。

mysql> describe my_site;

+--------------+--------------+------+-----+---------+----------------+

Field Type Null Key Default Extra

+--------------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| usrid | int(11) | YES | | NULL | |

| name | varchar(255) | YES | | NULL | |

| url | varchar(255) | YES | | NULL | |

| hour | varchar(13) | YES | | NULL | |

+--------------+--------------+------+-----+---------+----------------+

mysql> describe my_exclude;

+----------+--------------+------+-----+---------+----------------+

Field Type Null Key Default Extra

+----------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| usrid | int(11) | NO | | 0 | |

| url | varchar(255) | YES | | NULL | |

+----------+--------------+------+-----+---------+----------------+

このように作成しています。

2010/01/10 16:01:41
id:ken3memo No.2

ken3memo回答回数246ベストアンサー獲得回数772010/01/10 15:24:57

ポイント52pt

SQL文にorder by rand()

がランダム取り込みのために入っているから、必要ない(必要なのですが)全ての行にrand()の実行結果を付加して、ソートしているからだと勝手に妄想。

order by rand()

取り除いてはやいキーが付いているフィールドで普通の順番にして、

SQL発行後、n=MAXレコード数分の乱数を計算してから、mysql_field_seek とかで移動して値を取得、

2個目がほしいので、先頭行に忘れずカーソルを戻して、乱数発行、レコード移動、値の取得で2行ランダムっぽく取り出すとか。

以上、三流プログラマーの妄想でした。解決策の糸口・何かの参考となれば幸いです。

ダミーURL http://www.mysql.gr.jp/

id:kablog

アドバイス本当にありがとうございます。

実際にrand()をはずしてみましたが、実行速度はほとんど変化ありませんでした。

キーを特に意識していないのですが、その点が問題でしょうか。

2010/01/10 16:33:44
  • id:kablog
    補足です。
    not existsに書き換えてみましたがほとんど変わりませんでした。
    slow.logをみますと
    # Query_time: 14 Lock_time: 0 Rows_sent: 2 Rows_examined: 4237957
    # Query_time: 14 Lock_time: 0 Rows_sent: 2 Rows_examined: 4237957
    # Query_time: 14 Lock_time: 0 Rows_sent: 2 Rows_examined: 4237957
    # Query_time: 14 Lock_time: 0 Rows_sent: 2 Rows_examined: 4237957
    のように出ており、やはり、「my_excludeにはユーザーごと(usrid)に固有の除外URLを複数指定でき、対象URLからは省く」ロジックが上手でなく、巨大なデータを扱うことになってしまっていて時間がかかっているのではないかと想定しています。
    希望としては1秒未満に抑えたいと思います。

    topで確認するとmysqldがほぼ100%で張り付いている状態です。
  • id:ken3memo
    ダマされたと思って、つなげるテーブル双方に共通の重要フィールド[url]にインデックス(重複化でも)をそれぞれ作ってみても同じですか?
    本当にだましてしまったらスミマセン。
  • id:kablog
    インデックスの追加で劇的に早くなりました!ありがとうございます。

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

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

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

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