MySqlの高速化（100万件）を行いたいです。

Question

現在、簡単なアクセス解析を作っておりまして
ユーザーのアクセスしたレコード（accessanalyz_table）100万レコードほど
から期間指定した範囲を取得した後、
日付の連番に整形し、

• ユニークユーザー
• 観覧数

一覧を取得するSQL文を書いたのですが、
私の書き方ではとてもスピードが遅く。

indexの付け方や、
構造の書き換えを試してみたのですが、
スピードが変えることができませんでした。

もしチューニングできる部分や、
構造的に早くなる部分があればご指摘いただければ幸いです。

SELECT
    `date`,
    count(`id`) as `view_num`,
    count(distinct `user_cookie_id`) as `user_num`
FROM
    (
        select
            DATE_ADD("2019-08-26", INTERVAL (@i := @i + 1) -1 day) as `date`
        FROM
            `information_schema`.`columns`,
            (![イメージ説明](4b7750c5d1c4e12e61fc188fbd72e4b0.png)
                SELECT
                    @i := 0
            ) `gen_sub`
        WHERE
            DATE_ADD("2019-08-26", INTERVAL @i day) BETWEEN "2019-08-26"
            AND "2019-09-02"
    ) `date_generator`
    left join(
        select
            `day`,
            `id`,
            `user_cookie_id`
        FROM
            `accessanalyz_table`
        WHERE
            `day` BETWEEN "2019-08-26" AND "2019-09-02" + interval 1 day
    ) `myaccess` on DATE_FORMAT(`date_generator`.`date`, "%Y%m%d") = DATE_FORMAT(`myaccess`.`day`, "%Y%m%d")
GROUP BY
    `date`
ORDER BY
    `date` ASC

accessanalyz_table

試した事

Explanを行ったところ２行目のrowsが多い部分のrypeがALLになっていましたが、
こちらどのようにindexをつけるかわかりませんでした。

環境

• PHP 7.3.9
• phpMyAdmin 5.7.26

を使っております。

改善後のExplan

Comments

[yambejp]

create table+insert で検証できるサンプルデータを例示してもらう必要がありそうですが
explainの状況をみるとちょっと抜本的なリファクタリングが必要そうですね
サマリーテーブルをつくっておくとかで劇的に速くすることはできるかもしれません

[takamilk2]

yambejp様
ご回答。ありがとうございます。
サマリーテーブルも調べて考慮してみます。
ヒントをいただきありがとうございました！

Answer 1

集計は先に済ます（結合の件数が減る）というのをポイントにして組み立てなおしてみました。

また、(day, user_cookie_id)のマルチカラムインデックスを追加するとさらに改善されると思われる。

SELECT  `date`, coalesce(`view_num`, 0), coalesce(`user_num`, 0)
FROM (
        select  DATE_FORMAT(DATE_ADD("2019-08-26", INTERVAL (@i := @i + 1) -1 day), "%Y%m%d") as `date`
        FROM  `information_schema`.`columns`, (SELECT @i := 0) `gen_sub`
        WHERE   DATE_ADD("2019-08-26", INTERVAL @i day) BETWEEN "2019-08-26" AND "2019-09-02"
    ) `date_generator`
    left join (
        select  DATE_FORMAT(`day`, "%Y%m%d") as `day`
              , count(*) as `view_num`
              , count(distinct `user_cookie_id`) as `user_num`
        FROM  `accessanalyz_table`
        WHERE `day` BETWEEN "2019-08-26" AND "2019-09-02" + interval 1 day
        GROUP BY  DATE_FORMAT(`day`, "%Y%m%d")
    ) `myaccess` 
    on `date_generator`.`date` = `myaccess`.`day`
ORDER BY  `date` ASC

Comments

[takamilk2]

sazi様
わざわざコード修正してただき、ありがとうございます。。
インデックスはなぜかそこまでは差がでなかったのですが、
ご指摘いただいた構造の違いで1/5の早さにまで高速化できました!!
自身では何を行なってもだめだったので実行時間をみて目を疑いました！

仕組みまでわかりやすく教えていただき、
今後はこちらを考えながらsqlをより深く勉強したいと思います。
本当にありがとうございました。

[sazi]

改善後の実行計画を質問に追記して頂くと後学者の為にもなり、助かります。

[takamilk2]

sazi様
返答遅くなり申し訳ございません。
初心者のため専門的な用語などに詳しくなく、
改善後の実行計画というのはどのようなものでしょうか。
もしお手数でなければご教授お願いします。

[sazi]

実行計画=Explainです。
質問に揚げられているものの改善後の内容を希望しています。

[takamilk2]

大変失礼いたしました。上記に追加で画像を添付させていただきます。

[sazi]

ありがとうございました。