もうちょっと賢いSELECT文が書けないものでしょうか

質問

初めまして、最近SQLをかじり始めたぺーぺーです。
効率の悪いSELECT文しか書けずに困っています。

下記のテーブルsoftware_tableから、
・name列「oracle」
・version値が最大
のレコードに含まれるid(=3)を拾ってきたいのですが、自分の頭では副問い合わせを使う方法か、ソートを使う方法しか思いつきません。

問題は副問い合わせ・ソートを使うと計算コストが大きくなってしまうことで、できることなら副問い合わせ・ソートを使わずに解決したいのですが、何か方法はないものでしょうか？
よろしくお願い致します。

-----------------------------
software_table
id name version
1 oracle 1
2 oracle 2
3 oracle 3
4 postgres 1
5 postgres 2
6 postgres 3
7 postgres 4
-----------------------------

■副問い合わせを使った方法
SELECT id FROM software_table
WHERE
name = 'oracle' AND
version = (SELECT max(version) FROM software_table WHERE name = 'oracle');

■ソートを使った例
SELECT id FROM software_table
WHERE name = 'oracle' ORDER BY version DESC LIMIT 1 OFFSET 0;

-----------------------------

回答No.2

noname#249320 · ベストアンサー

数十万件だと毎回実行するにはなかなか厳しい感じですね。
状況から察するに, name にインデックスを張ってもあまり有効に働かないみたいですね。

アプリケーションを考慮して, name と versionの最大値 の組み合わせの抽出が頻繁に行われるようでしたら,
name と versionの最大値の二つの列を持つ参照用のテーブルを作成して, そこから参照するようにしてはどうでしょうか。

software_table に INSERT/UPDATE/DELETE が発生したら, 上記のテーブルをトリガを利用して書き換えればよいと思います。
# もちろんプログラム側で対応しても構いません

name と versionの最大値の組み合わせの参照がそれほど発生せず, アプリケーション的にも多少時間がかかるのが許されるならば,
副問い合わせやソートのやり方でもよいと思いますよ。

お礼

なるほどと思いました。で、nameとversionの最大値の組み合わせでの参照は頻繁に行われるため、試しに参照用のテーブルを作成して実行計画を見てみました。
　のですが、、速度を計測してみたところレスポンスは副問い合わせを使ったときの方が早いようです（レコード3万件で実験)。
　おそらくテーブルの結合に時間がかかってしまってることが原因ではないかと考えています。
　しかし今回はたまたまテーブル結合が無いケースでしたが、もともとのSELECT文でテーブル結合をする必要がある場合には、参照用のテーブルを作ったほうが早いかもしれませんね。

何度もアドバイスをくださってありがとうございます。もはや改善は無理っぽい感じですが、重要な部分なのでもう一日だけ頑張ってみようと思います^^;

回答No.1

noname#249320

基本的には副問い合わせやソートはコストのかかる処理です。
ただ, それはデータ件数が数十万, 数百万にもなった場合に実際に体感できる負荷として顕在化してきます。

software_table に格納されるデータ量の見積もりはどのくらいを想定しているのでしょうか？
格納されて高々数千件というレベルであれば, 書かれてある副問い合わせやソートを使っても全く問題ないと思います。

また, name属性で検索結果がかなり絞り込めるのであれば,
その後に MAX関数で最大値を求めようがソートで並び替えをしようがコストはほとんどかかりません。

副問い合わせやソートは必ずしも悪というわけではなく, 状況によって使い分ければよいと思います。

ちなみに GROUP BY を使って以下のようにもかけます。
が, 特に効率がいいということはありません ^^;

SELECT name, MAX(version) FROM software_table
WHERE name = 'oracle' GROUP BY name;

お礼

お返事ありがとうございます。

お返事をいただいて真っ先に思いましたのは、「SELECT文を書く場合、絞り込みの順番を意識することがすごく重要だ」ということです。絞込みやすい順にwhere句を記述していくとそれだけでパフォーマンスが上がるんですね。ＤＢエンジニアとしては当たり前の知識なのでしょうが、勉強になりました。

ただ「nameの絞込み自体のコストが大きい」ことは確かで、それが今回のケースでは問題になりそうだと感じました。メインの文と副問い合わせ文の両方でnameで絞込みをかけなければいけなくなっているので。。

なお、格納されるデータ量ですが、詳しくはわかりませんが数十万件くらいにはなる予定です。
お返事ありがとうございました^-^