複雑なクエリの自己結合について

はじめまして、SQLもMYSQLも初心者です。　独学で、1，2か月といったところでしょうか
MYSQL5.5.を使用しています。

早速ですが質問です。

自己結合のもとになるクエリが複雑？な場合どうすれば負荷の少ないクエリが組めるでしょうか？

自己結合したいクエリが、相関サブクエリをつかっているためViewが作れないので
素人考えで、もととなるクエリをSQL中に何回も記述してみましたが、
メモリがオーバーフローしてしまいます。
複雑なクエリをスマートに自己結合する方法はないのでしょうか？

それとも、そもそも、最初に自己結合や外部結合でめいいっぱいデータを展開し、それを相関サブクエリ.etcで、漸次、絞り込んでいくほうが、SQLの手段としては正しいのでしょうか？

他で上記のクエリのかなりの部分を使用するので、再利用できないかと思って組んでみましたが
行き詰まり、色々と自信がなくなってきました、アドバイスをお願いします。

以下やりたいことです
暇だったら見てください。

勉強も兼ね、カレンダーアプリで担当者ごとの抱えているプロジェクトと、忙しさ、割ける時間、効率性
を可視化することを目的としたソフトをつくろうと思っています。そこで、日にちごとに、不特定の時間帯から、時間の重複を許して、複数の時間帯を除した、あまりの時間を求めるクエリを組もうと思い、web上で勉強して、相関サブクエリと、自己結合とHAVING文などを組み合わせれば、実現可能なことがわかりました。そこで、相関図を書いて、実際に作って見たのですが、上記のようになった次第です。ぜひ、皆様のお知恵を拝借したいしだいです。よろしくお願いします。

No.2 ベストアンサー 回答者： yambejp 回答日時： 2012/10/01 19:35

データの結合は相乗的にデータが増えるので、自己結合などで

条件設定を冗長にやっていると処理時間が膨大になったり
メモリを食い過ぎたりなど当然想定しなくてはいけませんね

＞独学で、1，2か月

で、このレベルの質問ができるとすると、相当基礎的なスキルがあるかとは
思いますが、まずはSQLの基本である正規化について知識を深めて、
インデックスなどをためしながらexplainでテストしていく
というのが流れになると思います。

また簡易なデータ参照についてはエンジンも思い切ってMyISAMなどで
処理することで高速化をはかるとか、データ更新がおおくなかったり
完全なリアルタイム性が必要でないものは中間テーブルをつくって
高速化することはできるかと思います。

プロジェクト管理についてはガントチャートやパートチャートの考え方を理解し、
プロジェクトのパートごとに人月設定をしたうえで、アサインするユーザーに
配分の係数を掛けていくと進捗がだせると思いますので
プロジェクトテーブル、ユーザーテーブル、プロジェクト＝ユーザーアサインテーブル
などに分けて処理すれば効率的にいけるかもしれません。
期間の処理はプロジェクトに寄せるか、さらに別テーブルで管理するかは
出力したい帳票によりますね

この回答へのお礼

回答、そしてアドバイスありがとうございます。

>>完全なリアルタイム性が必要でないものは中間テーブルをつくって高速化
やっぱりそれしかないのですかね、テーブルを全更新しているとスピードが使用に耐えないので（explainでテストしてみるべきですけど）

トリガつけて差分更新かぁ、そうするとデータの整合性が心配になってくるから、どこでどんなチェックをいれればいいのかなー。それに、メモリ制約結構外れて、汎用性あげられるよな、設計見直さな。むしろ仕様から変更したほうがいいのか？
みたいな
全行程を自分一人でやる（さらに使用者（のうちの一人）も、保守も、利益を得るのも自分）特有の悩みが。むしろ贅沢な悩みかもしれませんがｗ

抜本的な仕様変更も視野に入れ考えてみます。

あと、ガントチャートいいですよね。
プロジェクトといっても少人数で、そんな複雑じゃなく、むしろ複数同時進行で、突発的に予定が入る
という仕様上ガントチャートは考えておりませんでした、が余力があれば、インターフェース兼出力帳票として検討してみたいと思います。夢がひろがります、また、仕様が変わりそうですが。

お礼日時：2012/10/02 11:47

No.1 回答者： SherlockHolmes2 回答日時： 2012/10/01 19:21

MySQLはサブクエリに弱いという話を良く聞きます。

※参考URLを貼っておきます。

私の場合、複雑な処理を書く場合は、後でメンテナンスする人の事も考えてなるべくシンプルに書く努力をします。

複雑なSQL文を使って一発で答えを得るより、簡素なSQLを複数回実行させたほうが、見た目にもキレイになるし、
後でのメンテナンスも楽になると思っているからです。

すみません、回答になっていないかも知れませんが・・・。

参考URL：http://el.jibun.atmarkit.co.jp/garyotensei/2012/ …

この回答へのお礼

早速の回答ありがとうございます。

>>MySQLはサブクエリに弱い
indexの取り方がところどころおかしいとも聞きます、それも関係するのでしょうか。
webの評価を見ている限りフリーでは最強だと思うのですが、商用DBも考えたほうがいいのか。

>>後でメンテナンスする人の事も考え、なるべくシンプルに書く努力
>>複雑なSQL文を使って一発で答えを得るより、簡素なSQLを複数回実行させたほうが、見た目にもキレイになるし、後でのメンテナンスも楽になると思っているからです。

耳が痛いですｗ自分用に、ここまで集合を広げ、こういった特性関数で絞り込むみたいな設計で可視化は
やってますが、後の人ことは考えてないです、精進します。

お礼日時：2012/10/02 10:18