GROUP BYによる集計後のソートの高速化にアイデアはないでしょうか？

質問

ウェブサービスはいろいろ作ったことはあるのですが、分析系は作ったことがなく、DB構造について悩んでるところであります。

現在の課題は、商品ごとの売り上げ集計後に、それをランキング表示して表示する、というところなんですが、

select 商品ID,SUM(売り上げ)as`sales` from 売り上げテーブル group by 商品ID order by sales DESC limit 0,30

というものですが、やはり sales にインデックスが使えないのでtemporary table と file sort がでてきて遅くなってしまいます。
order by をはずすとやはり高速にレスポンスは返ってきます。

日別、月別、などのサマリーテーブルは作ってはいるのですが、単月ではなく、複数月指定での集計だとサマリーはないので集計をしないといけなくなります。

基本的なことなんだと思いますが、諸先輩方はどういったやり方で解決されておられるのでしょうか？

なお、試験段階の現在は、売り上げテーブルには400万件、商品データは6万件あります。ですので、order by をはずした場合、6万件ほどがヒットし、それを(file sortで)並び替えてる状態だと思います。

回答No.2

hrm_mmm

＞複数月指定での集計

月別集計した物を、unionで複数月連結して、sum をとればよさそうな要件にも思えるのだけど？

お礼

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

なるほど、単月毎に売り上げ順に並び替えたものをUNIONでつなげて、それをSUMする、という逆のアプローチということですかね。
UNIONしたあとにGROUP BYはできましたっけ。その後にでてくる値がちゃんと売り上げ順になってるかもやってみないとわかりませんね。ちょっと試してみます。

回答No.1

yambejp

400万件のデータをダミーでつくってテストできる環境ではないため
なんともいえませんが、order by している以上テンポラリはしかたないのでは？
ヒープにおとしてソート処理すれば若干はやくなるかもしれませんが
あまり期待できないかなぁ・・・

お礼

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

そうですね、私もHEAPは考えたのですが、じゃあいつの段階で消すのか、というのもありますし、放置しておくとメモリーを食い尽くしそうな気もしますし、いろいろ試行が必要ですね。
結局、テンポラリもメモリ上だとは思いますが、設定でメモリをしっかり割り当てて、最適化することが大切ですよね。