たびたび田中です。
こんばんは。

無理矢理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
>>> ****************************************
>>>
>>>
>>>
>>
>>