SQL_Tips001 - ErogameScape -エロゲー批評空間- の備忘録

SQLの実行コスト

WHERE句の活用

さて、以下は、ゲーム名／中央値／ユーザーの得点／登録日／ユーザー名を取得するために、ゲームリスト(gamelist)とユーザーレビュー(userreview)をゲームIDで連携させて、登録日の新しい順かつゲームのフリガナ順で、100件のデータを返すSQLのサンプルです。

SELECT gl.gamename, gl.median, ur.tokuten, ur.tourokubi, ur.uid
  FROM gamelist AS gl, userreview AS ur
 WHERE gl.id = ur.game
 ORDER BY ur.tourokubi DESC, gl.furigana
 LIMIT 100
 

実行コストは、(cost=59640.11..59640.36 rows=100 width=73)となり、コスト制限の60000ギリギリで動作しました。(※2010/11/27現在)
ですが、これはいつ60000を超えてもおかしくありません。ゲームが新しく追加され、レビューが新しく追加される度に、実行コストは増加していきます。参照するデータ量の増加と、実行コストの増加は直結しているわけです。
では、参照するデータを減らしてあげれば、当然、実行コストも下がるわけです。

userreviewテーブルには未プレイの情報も格納されています。今回の場合、未プレイで得点のないデータを参照しても意味がありません。
よって、WHERE句で「プレイ済みに限る」という条件を追加します。
(ur.play = TRUE　既存のSQLでよく見かける、ur.tokuten IS NOT NULLだと、プレイ済みで点数無しのデータも無視してしまいます。)
次に、userreviewテーブルへの書き込みをSQLで調べると、平均185件／日となっています。今回、出力したいのは、新着100件ですので余裕を見ても一週間程度の期間のデータに絞り込めば充分だという予想が出来ます。
よって、WHERE句で「登録日が現在から遡って7日以内」という条件を追加します。
(ur.tourokubi > CURRENT_DATE - CAST('7 days' AS interval)　日時の演算は、CURRENT_DATE - 7 とするよりもCASTを使って型変換した方が良い。※暗黙の型変換は避ける)

SELECT gl.gamename, gl.median, ur.tokuten, ur.tourokubi, ur.uid
  FROM gamelist AS gl, userreview AS ur
 WHERE gl.id = ur.game AND ur.play = TRUE AND ur.tourokubi > CURRENT_DATE - CAST('7 days' AS INTERVAL)
 ORDER BY ur.tourokubi DESC, gl.furigana
 LIMIT 100
 

これで実行コストは、(cost=2083.39..2083.64 rows=100 width=73)となりました。実行コスト制限なんて余裕ですね。

【管理】POSTボタンを生成しました。