田中様　お返事が遅れまして済みませんでした。
コメント有難うございました。

教えて頂いたSQLですが、
この処理はバッチ処理的なレポートではなく、リアル表示の照会画面で使用する
ものです。
そもそもこれだけの処理時間が掛かることを想定せず、なんとかなるだろう位の
考えで、
DB設計をしていたのが間違いかもしれません。

早速、教えて頂いたSQLをサーバー上で実行してみました。
さすがに、31回のLEFTJOINは非力なサーバーでは厳しかったようです。
10分位、反応がありませんでした。

使用している言語も、COLDFUSIONというアプリケーションサーバーを使っている
のも、
ネックになっているのかもしれません。PHPよりは遅いという情報もあります。

それから、npdt という列は、CHAR(8)で、yyyymmddの形式で入っているため、止
むなく
Mid(CAST(npdt AS CHAR),7,2)で切りだしています。

ご丁寧なコメント、感謝致します。
-----------------------------------------------
初穂太郎


(2012/04/21 2:05), Tsubasa Tanaka wrote:
> 何度も申し訳ありません。
> 間違いに気付きました。
>
>> SELECT nm,h101,h102,...,h131
>> FROM (
>>  SELECT nm,COUNT(nm) AS h101
>>  FROM nippo
>>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
>> DAY(npdt) = 1
>>  GROUP BY nm
>> ) AS t101
>> ...
> SELECT d.nm,h101,h102,...,h131
> FROM (
>  SELECT DISTINCT nm AS nm
>  FROM nippo
>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd#
> ) AS d
> LEFT JOIN (
>  SELECT nm,COUNT(nm) AS h101
>  FROM nippo
>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
> DAY(npdt) = 1
>  GROUP BY nm
> ) AS t101 USING(nm)
> LEFT JOIN ...
> でした。
>
> h101の列を作るサブクエリの前に全nmを取ってこないと、DAY(npdt) = 1の時のnm以外が
> JOINしても現れてきませんでした。。
>
> また、Accessのサブクエリは()ではなく[].(ピリオドつき)で囲まなければならなかったりした気がします。
> 確かバージョンによっては()で囲ってもいけた気がしますが、手元にAccessがないのでごめんなさい。
> "access from句 サブクエリ"などで検索していただければ出てくると思います。
>
> また、訪問回数が0の時の値がNULLになるので、
> 0にしたければCOUNT(nm)をIIF(ISNULL(COUNT(nm)),0,COUNT(nm))などに書き換えればいけるかなと思います。
>
> よく考えてから投稿する様に致します。
> お騒がせ致しました。
>
>
> ts. tanaka//
>
> 2012年4月21日1:06 Tsubasa Tanaka <yoku0825@xxxxxxxxxx>:
>> たびたび田中です。
>> こんばんは。
>>
>> 無理矢理INDEXを利かせる様なSQLを考えてみました。
>> INDEX(npdt,sycd,nm)が定義されている前提です。
>>
>> SELECT nm,h101,h102,...,h131
>> FROM (
>>  SELECT nm,COUNT(nm) AS h101
>>  FROM nippo
>>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
>> DAY(npdt) = 1
>>  GROUP BY nm
>> ) AS t101
>> LEFT JOIN (
>>  SELECT nm,COUNT(nm) AS h102
>>  FROM nippo
>>  WHERE npdt >= #from_ymd# AND npdt < #to_ymd# AND sycd = #sycd# AND
>> DAY(npdt) = 2
>>  GROUP BY nm
>> ) AS t102 USING(nm)
>> LEFT JOIN (
>> ...
>> ) AS t131 USING(nm);
>>
>> npdtがDATE型、DATETIME型、TIMESTAMP型のいずれかと仮定して、
>> Mid(CAST(npdt AS CHAR),7,2)='xx'
>> を
>> DAY(npdt) = xx に置き換えています。
>> ご参考までに。。
>>
>>
>>> …が、経験則としては一度MySQLからデータをインポートしてAccess上にテーブルを作ってあげて、
>>> そのテーブルを見に行くクエリにした方がずっと速いと思います。
>> 書きそびれてしまいましたが、インポート後にインデックスを貼るのをお忘れなく。
>>
>> それでは。
>>
>>
>> ts. tanaka//
>>
>> 2012年4月20日22:05 Tsubasa Tanaka <yoku0825@xxxxxxxxxx>:
>>> はじめまして、田中と申します。
>>>
>>>> id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
>>>> 1    SIMPLE    t@nippod    range    idx1    idx1    26    NULL    111    Using where; Using temporary; Using filesort
>>> Using temporaryはテンポラリテーブルを推奨されているのではなく、
>>> クエリ処理中にメモリ内に収まらなかったので、テンポラリテーブルを作りましたよという事後報告です。
>>>
>>> 一番はメモリ内にクエリの結果が全て収まることですが、
>>> テンポラリテーブルはまずはメモリ上に作られますので、この段階であればそこまで遅くなりません。
>>> 最悪のシナリオはテンポラリテーブルもメモリ内に収まらず、
>>> DISK上にテンポラリテーブルが作られてしまうことです。
>>> ただし、メモリ上で済んだのかDISKまで書いてしまったのかはEXPLAINの結果からだけでは判断できません。
>>>
>>> MyISAMテーブルの場合、
>>> read_buffer_size > 結果セットの大きさ　の場合にはテンポラリテーブルは使われません。
>>> InnoDBテーブルの場合は明示的なステータスは無かった様な気がしますが、
>>> innodb_buffer_pool_sizeという変数が大きく関連しています。
>>> まずはどちらのテーブルを使っているかで、これらの値を（可能ならば）大きくして下さい。
>>>
>>> 結果セットの大きさを知る方法は無いので、適当に大きくして下さい。
>>> DB専用のサーバであれば、read_buffer_sizeは全メモリの25%〜50%、
>>> innodb_buffer_pool_sizeは全メモリの80%が目安といわれています。
>>>
>>>
>>>
>>> …が、経験則としては一度MySQLからデータをインポートしてAccess上にテーブルを作ってあげて、
>>> そのテーブルを見に行くクエリにした方がずっと速いと思います。
>>> 月に1度の集計であれば、この方法はどうでしょうか？
>>>
>>> may your problem is solved!
>>>
>>>
>>> ts. tanaka//
>>>
>>> 2012年4月20日17:58 ram <ram@xxxxxxxxxx>:
>>>> 中川様
>>>> 重ね重ねコメント有難うございます。
>>>>
>>>>> お使いのSELECT文のEXPLAINの結果は出せますか？
>>>> EXPLAINは使ったことが無いのですが、
>>>> SELECT文の先頭にEXPLAINと付けて、リダイレクトで実行した結果、以下のように出ます。
>>>> t@nippod というのが実テーブル名で、
>>>> idx1というのが今張ってあるインデックスです。
>>>>
>>>> id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
>>>> 1    SIMPLE    t@nippod    range    idx1    idx1    26    NULL    111    Using where; Using temporary; Using filesort
>>>>
>>>> idx1は　WHERE句の中に含まれている列とソートの順である　sycd, npdt, nm　です。
>>>>
>>>> この Using temporary が気になりますが、CREATE TEMPORARY TABLEで、
>>>> テンポラリテーブルを使ったほうが処理が効率的という意味なんですね？
>>>>
>>>>> 後、
>>>>> read_buffer_size
>>>>> read_rnd_buffer_size
>>>>> sort_buffer_size
>>>>> はそれぞれどんな値を設定されていますか？
>>>> read_buffer_size    65536
>>>> read_rnd_buffer_size    262144
>>>> sort_buffer_size    262144
>>>>
>>>> で、デフォルトから変更していません。
>>>>
>>>> ------------------------------------------
>>>> 初穂　太郎
>>>>
>>>>
>>>> (2012/04/20 15:24), 中川 貴 wrote:
>>>>> (2012/04/20 13:58), ram wrote:
>>>>>> 中川様　コメント有難うございました。
>>>>>>
>>>>>> このインデックスは設定済みですが、ほとんど効果がありませんでした。
>>>>>> 情報がお伝え足りなくて申し訳ないです。
>>>>>>
>>>>>> もう一度、外してから、その前後でパフォーマンス比較をしましたが、
>>>>>> 平均応答時間は、ほとんど変わっていませんでした。
>>>>>>
>>>>>> また、先に件数を絞り込んだほうがよいかと思い、サブクエリーで抽出した後に、
>>>>>> GROUP BYを行ってもみましたが、これも、ほとんど変わらず、どちらかというと、
>>>>>> わずかにパフォーマンスが落ちました。
>>>>>>
>>>>>> SELECT x.nm,
>>>>>> sum(case when x.dd = '01' then 1 else 0 end) as 'h101',
>>>>>> sum(case when x.dd = '02' then 1 else 0 end) as 'h102',
>>>>>> （中略）
>>>>>> sum(case when x.dd = '31' then 1 else 0 end) as 'h131'
>>>>>> FROM (SELECT nm,Mid(CAST(npdt AS CHAR),7,2) as dd
>>>>>> FROM nippo
>>>>>> WHERE sycd = #ck_sycd# and npdate>= #from_ymd# and npdate<  #to_ymd# ) as x
>>>>>> GROUP BY x.nm ORDER BY x.nm
>>>>> お使いのSELECT文のEXPLAINの結果は出せますか？
>>>>>
>>>>> 後、
>>>>> read_buffer_size
>>>>> read_rnd_buffer_size
>>>>> sort_buffer_size
>>>>> はそれぞれどんな値を設定されていますか？
>>>>>
>>>>> ****************************************
>>>>> コープ情報システム株式会社
>>>>> 運用事業部
>>>>> コーププラザサービスデスクグループ
>>>>> 中川　貴
>>>>> 175-0094
>>>>> 東京都渋谷区渋谷3-29-8 コーププラザ5F
>>>>> TEL 03-5778-8010
>>>>> E-MAIL takashi.nakagawa@xxxxxxxxxx
>>>>> ****************************************
>>>>>
>>>>>
>>>>>
>>>>
>