低負荷のSQL文でランキング表を作成したい

Question

レコード数が１万ほどあるテーブルのランキング表を作ったのですが、ユーザーの同接が1000人を超えることもあり、負荷がかかり過ぎてレンタルサーバーから制限がかかってしまいました。

$result = $wpdb->get_results("SELECT(SELECT count(*) FROM $wpdb->ranking_table as p1 WHERE p1.rankingpt > p.rankingpt) + 1 as rank1,p.name,p.rankingpt FROM $wpdb->ranking_table as p ORDER BY rank1");

上記SQL文のORDER BYでの負荷が高すぎるようなのですが、負荷の少ない書き方はないでしょうか？

利用データベースはMySQL 5.6です
よろしくお願いします。

Comments

[m.ts10806]

テーブル数が１万？
レコード数ではなく？
確かにテーブル数が１万はあり得ないですが
レコード数１万なら大して多くは感じません。
SQLの問題なんでしょうか？
「負荷がかかり」はどこに負荷がかかっていたか確認はされてますか？
SQLの負荷が高いというのはどこで得た情報なのでしょうか。

[pan2taicho]

すみません、レコード数の間違いです。
「"ranking_table" テーブルの負荷が高いから制限する」とだけ、レンタルサーバーの会社からメールが届きました。
ranking_tableに関わるSQLがシンプルなINSERT文と今回のSELECT文しかないので、このSQL文での負荷だと判断しました。

[m.ts10806]

データやテーブル定義がないので他者には何とも言えない部分はあるのですが、
実行計画とってみた方がいいかもしれませんね。
サブクエリの重ね方は気にはなります。

[sazi]

ランキングは１万件全て表示しているのですか？

Answer 1

ORDER BY よりも、１レコードずつカウントするのがまずかったようです。
下記記事を参考に、変数を使ったSQL文を使ったら解決しました。
ありがとうございましたm(_ _)m

【参考記事】
https://qiita.com/hmuronaka/items/1afc132ddf400363efc2

Comments

[sazi]

limitは付けずに解決したのですか？

[pan2taicho]

limitもつけました。
ただ、limitをつけただけでは解決しなかったので、変数を使ったSQL文を使ったところ劇的に負荷が解消されました。

Answer 2

ランキング全て表示というのは無理があるので、TOP100とか限定するしかないかと思います。
順位を表示したとしても、rankingptの降順には違いないでしょうから、以下の様にしてみては。
※そもそもORDER BY rank1としているので並び替えの為に、全ての順位を求めていると思われる。

order by rankingpt desc limit 100

そうすれば、limitの件数分に対してだけ、ランク順位の相関問合せが行われるので、トータルコストは、limit件数次第になると思います。

Comments

[pan2taicho]

ご回答ありがとうございます。limitで制限かけて、さらにWHEREで最新の１ヶ月間のみに絞りました。ありがとうございました！