MySQLでクロス集計をするクエリの実行速度検証

実行速度の簡単な検証をしてみました。
単価＊数量を計算して2次元のデータを取得するSQLです。

select
pref,
sum(case when date = '2015-10-01' then price * qty else 0 end) `2015-10-01`,
sum(case when date = '2015-10-02' then price * qty else 0 end) `2015-10-02`,
sum(case when date = '2015-10-03' then price * qty else 0 end) `2015-10-03`,
sum(case when date = '2015-10-04' then price * qty else 0 end) `2015-10-04`,
sum(case when date = '2015-10-05' then price * qty else 0 end) `2015-10-05`,
sum(case when date = '2015-10-06' then price * qty else 0 end) `2015-10-06`,
sum(case when date = '2015-10-07' then price * qty else 0 end) `2015-10-07`,
sum(case when date = '2015-10-08' then price * qty else 0 end) `2015-10-08`,
sum(case when date = '2015-10-09' then price * qty else 0 end) `2015-10-09`,
sum(case when date = '2015-10-10' then price * qty else 0 end) `2015-10-10`,
sum(case when date = '2015-10-11' then price * qty else 0 end) `2015-10-11`,
sum(case when date = '2015-10-12' then price * qty else 0 end) `2015-10-12`,
sum(case when date = '2015-10-13' then price * qty else 0 end) `2015-10-13`,
sum(case when date = '2015-10-14' then price * qty else 0 end) `2015-10-14`,
sum(case when date = '2015-10-15' then price * qty else 0 end) `2015-10-15`,
sum(case when date = '2015-10-16' then price * qty else 0 end) `2015-10-16`,
sum(case when date = '2015-10-17' then price * qty else 0 end) `2015-10-17`,
sum(case when date = '2015-10-18' then price * qty else 0 end) `2015-10-18`,
sum(case when date = '2015-10-19' then price * qty else 0 end) `2015-10-19`,
sum(case when date = '2015-10-20' then price * qty else 0 end) `2015-10-20`,
sum(case when date = '2015-10-21' then price * qty else 0 end) `2015-10-21`,
sum(case when date = '2015-10-22' then price * qty else 0 end) `2015-10-22`,
sum(case when date = '2015-10-23' then price * qty else 0 end) `2015-10-23`,
sum(case when date = '2015-10-24' then price * qty else 0 end) `2015-10-24`,
sum(case when date = '2015-10-25' then price * qty else 0 end) `2015-10-25`,
sum(case when date = '2015-10-26' then price * qty else 0 end) `2015-10-26`,
sum(case when date = '2015-10-27' then price * qty else 0 end) `2015-10-27`,
sum(case when date = '2015-10-28' then price * qty else 0 end) `2015-10-28`,
sum(case when date = '2015-10-29' then price * qty else 0 end) `2015-10-29`,
sum(case when date = '2015-10-30' then price * qty else 0 end) `2015-10-30`,
sum(case when date = '2015-10-31' then price * qty else 0 end) `2015-10-31`
 
from test01 where
    date >= '2015-10-01' and
    date < '2015-11-01'
group by pref
order by field(pref,'Hokkaido','Aomori','Iwate','Miyagi','Akita','Yamagata','Fukushima','Ibaraki','Tochigi','Gunma','Saitama','Chiba','Tokyo','Kanagawa','Niigata','Toyama','Ishikawa','Fukui','Yamanashi','Nagano','Gifu','Shizuoka','Aichi','Mie','Shiga','Kyoto','Osaka','Hyogo','Nara','Wakayama','Tottori','Shimane','Okayama','Hiroshima','Yamaguchi','Tokushima','Kagawa','Ehime','Kochi','Fukuoka','Saga','Nagasaki','Kumamoto','Oita','Miyazaki','Kagoshima','Okinawa')

テーブルのカラムは下記です。

`id` INT(11) NULL DEFAULT NULL,
`pref` VARCHAR(9) NULL DEFAULT NULL,
`date` DATE NULL DEFAULT NULL,
`price` INT(11) NULL DEFAULT NULL,
`qty` INT(11) NULL DEFAULT NULL

データ件数は5,000件。下記のようなデータが入っています。

id	pref	date	price	qty
1	Kagoshima	2015-10-30	98	10
2	Yamanashi	2015-11-17	86	15
3	Saga	2015-10-15	62	4
:	:	:	:	:

price：単価　qty：数量　pref：商品名？（都道府県にしてしまいましたがこういう商品だと考えてください。）

まずは上記のSQLを10回実行した結果は、平均 0.0360 sec。

下記のSQLで比較しました。

select
pref,
date,
sum(price * qty) sum
from test01 
where
    date >= '2015-10-01' and
    date < '2015-11-01'
group by pref,date
order by field(pref,'Hokkaido','Aomori','Iwate','Miyagi','Akita','Yamagata','Fukushima','Ibaraki','Tochigi','Gunma','Saitama','Chiba','Tokyo','Kanagawa','Niigata','Toyama','Ishikawa','Fukui','Yamanashi','Nagano','Gifu','Shizuoka','Aichi','Mie','Shiga','Kyoto','Osaka','Hyogo','Nara','Wakayama','Tottori','Shimane','Okayama','Hiroshima','Yamaguchi','Tokushima','Kagawa','Ehime','Kochi','Fukuoka','Saga','Nagasaki','Kumamoto','Oita','Miyazaki','Kagoshima','Okinawa')
,date

同じく10回実行した結果は、平均 0.0125 sec。

色々な条件によって違うかと思いますが、筆者がためした環境ではクロス集計のSQLの方がやや遅いようです。今回は5000件のデータでしたが、増えれば更に遅くなると思います。プログラム側の実行速度は検証しておりませんが、MySQL側が重くなるようなら避けたほうが良いかも知れません。

該当する日付のデータがなくても 0 として出力してくれるので、mysqlクライアントソフトで実行したときに見やすい。という利点があります。データの確認には良いと思います。

検証で使用したデータは下記で取得しました。

Mockaroo - Random Data Generator
https://www.mockaroo.com/

出力するフォーマットやカラムのデータタイプが色々選べるので重宝しました。

MySQLでクロス集計をするクエリ
http://web.loft-net.co.jp/lofttecs/mysql_crosstab/