僕の好きな TEMPORARY TABLE

MySQL Advent Calendar 2019 の19日目です。
アドベントカレンダーをたまたま見にいったら、たまたま今日と明日が空いていたので、
なんとなく飛び入り参加させていただきました。

はじめに

みなさんクエリを書く時JoinしまくったりSubqueries入れまくったりでめちゃくちゃ長いクエリ作ってませんか？🙈
あとで見直して『なんだこの長いクエリ…』とか、説明だけ読んで『あーなるほどそのデータとってくるためのクエリね！なるほどー！読みたくねぇ…』とかなってませんか？？？🙉
まぁ仕方ないこともあるんですが…🙊
そんなあなたに TEMPORARY TABLE の紹介です。

なにそれ

その名の通り、セッションを繋いでいる間だけ存在する一時テーブルのことです。
テーブルをサクッと作れて、
必要な情報のみを突っ込みクエリをわかりやすく分割できる、
MySQLではもちろんPostgreSQLやOracleでも使える、
なんなら INDEX も張れる
とっても優れた機能です🤗
設定すればRDSでも動かせます。

ただし色々と罠があり、権限ないと使えなかったり、他の人から見えなかったり、1クエリ内で2回呼び出せなかったり、セッション切れてテーブルつくりなおしになったり、性能にものを言わせたり（実は遅くなったり）、リソース食ったりするので、本番で使うときは注意が必要（詳しいことは知らんが😇

つくってみる

基本的には CREATE TABLE と同じように作れます。
ので、普通に定義してみたり

CREATE temporary TABLE `tmp_pref` (
  `pref_id` INT(11) NOT NULL,
  `string`  VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

構造を真似たり

CREATE temporary TABLE `tmp_pref` like pref;

なんなら SELECT したものをそのまま INDEX 付きで突っ込めます⭐️

CREATE temporary TABLE `tmp_pref`
(
    primary key pr (pref_id),
    INDEX string_key (string)
)
SELECT *
FROM station_all
WHERE pref_id = 1 ;

分解してみる

さてさて本題
今年の1月中に本を注文した男性の最寄駅の第1住所を人数付きで知りたい。
というクソみたいなクエリを考えてみます👏
（実際にある駅データさんのデータとken_all、その他適当なテーブルを頭の中で構成したのであっているか怪しい。

SELECT ka.address1,
       count(ka.address1)
FROM purchase p
JOIN user u
    ON u.user_id = p.user_id
JOIN station_all sa
    ON sa.station_cd = u.station_cd
JOIN ken_all ka
    ON ka.post_code = sa.post
WHERE p.create_datetime
    BETWEEN '2019-01-01 00:00:00'
        AND '2019-01-31 23:59:59'
    AND p.item_type IN (
       SELECT type_id
       FROM item_type
       WHERE type_name LIKE '%book' 
    ) 
    AND u.sex = 1
GROUP BY  ka.address1
;

1個ずつ分解してみます。
とはいえ分解量は INDEX が効いている範囲とか、ここまでは一つだろとか、人それぞれだと思うので適当にわかりやすそうな単位で…

まずは今年1月に購入履歴のあるユーザ情報から☝️
ここでitem_type に INDEX を張ってやります。（もともとない想定で

CREATE temporary TABLE `purchase_uesr_tmp`
(
    INDEX item_type_key (item_type)
)
SELECT u.user_id,
       u.station_id,
       p.item_type, 
FROM purchase p
JOIN user u
    ON u.user_id = p.user_id
WHERE p.create_datetime
    BETWEEN '2019-01-01 00:00:00'
        AND '2019-01-31 23:59:59'
    AND u.sex = 1
; 

INDEX を張ってやったので本を買った人をみていきましょう✌️

CREATE temporary TABLE `book_purchase_uesr_tmp`
(
    INDEX station_cd_key (station_cd)
)
SELECT station_cd
FROM purchase_uesr_tmp put
WHERE 
    item_type IN (
       SELECT type_id
       FROM item_type
       WHERE type_name LIKE '%book' 
    ) 
;

ではあとやっちゃいたいところですが、一旦 address1 に INDEX を張ってみます🤟

CREATE temporary TABLE `book_purchase_uesr_address_tmp`
(
    INDEX address1_key (address1)
)
SELECT ka.address1,
FROM book_purchase_uesr_tmp bput
JOIN station_all sa
    ON sa.station_cd = bput.station_cd
JOIN ken_all ka
    ON ka.post_code = sa.post
;

らすと

SELECT address1,
       count(address1)
FROM book_purchase_uesr_address_tmp
GROUP BY  ka.address1
;

まとめ

さてさて分解してみていかがだったでしょうか？？？
最初より長くなりすぎて逆に分からんって？？それは書いていて思ったので、これくらい短いとまとめた方がわかりやすいかもしれませんね…
さらにいうと MySQL8.0 から WITH句 つかえますからね。そんなに使用頻度ないかもしれませんよね。
とはいえ INDEX 貼ることで速くなったりするので、実行計画とにらめっこしながら分解作業するのも速度を上げる1つの手ですよ😭

ということで皆さんもたのしい TEMPORARY TABLE 生活を🤝

おまけ

以前いた職場でDBにioDriveを使っていたため、W/Rがとっても速くて（その時代では）それなら TEMPORARY TABLE 使った方が早いと すごい人Sさん 言われて初めて存在を知りました。
HDDとか書き込み遅いとtmp書き出しに時間が…なんてこともあるはずなので一概に早くあるとは言えません。ご注意ください。

参考になれば資料

https://dev.mysql.com/doc/refman/8.0/en/create-table.html
https://dev.mysql.com/doc/refman/8.0/en/create-temporary-table.html
https://www.post.japanpost.jp/zipcode/download.html
https://www.ekidata.jp/