MySQLのパフォーマンス改善(共通テーブル式が使えず、サブクエリが大量発生)

Question

■環境
MySQLバージョン：5.6.36

■質問(※2019/11/21 00:00 大きく修正しております。申し訳ありません。。)
以下のような「サンプルデータ」、「期待する結果」、「(期待結果は取得できるが、時間がかかる)SQL」において、パフォーマンスが遅く困っています。
改善方法をご存知の方がいらしたら、ご教示お願いできませんでしょうか。

●サンプルデータ
Name,X,Y,create_time
(A,0,0,'2019-11-20 01:00'),
(A,0,1,'2019-11-20 01:10'),
(B,0,1,'2019-11-20 01:20'),
(A,0,1,'2019-11-20 01:30'),
(A,1,1,'2019-11-20 01:40'),
(B,1,1,'2019-11-20 01:50'),
(A,0,1,'2019-11-20 02:00'),
(A,0,1,'2019-11-20 03:50'),
(B,0,1,'2019-11-20 05:50')

●期待する結果…当該Nameの、それまでのパターン別発生回数が知りたいです
|Name|X|Y|count|create_time|0-0|0-1|1-0|1-1|
|A|0|0|2019-11-20 01:00|0|0|0|0|
|A|0|1|2019-11-20 01:10|1|0|0|0|
|B|0|1|2019-11-20 01:20|0|0|0|0|
|A|0|1|2019-11-20 01:30|1|1|0|0|
|A|1|1|2019-11-20 01:40|1|2|0|0|
|B|1|1|2019-11-20 01:50|0|1|0|0|
|A|0|1|2019-11-20 02:00|1|2|0|1|
|A|0|1|2019-11-20 03:50|1|3|0|1|
|B|0|1|2019-11-20 05:50|0|1|0|1|

Select Name,X,Y,create_time,(
                            Select count(X=0 and Y=0)
                            From Table as B
                            Where A.Name=B.Name
                             And A.create_time > B.create_time
                           ),
                           (
                            Select count(X=0 and Y=1)
                            From Table as B
                            Where A.Name=B.Name
                             And A.create_time > B.create_time
                           )
                           (
                            Select count(X=1 and Y=0)
                            From Table as B
                            Where A.Name=B.Name
                             And A.create_time > B.create_time
                           ),
                           (
                            Select count(X=1 and Y=1)
                            From Table as B
                            Where A.Name=B.Name
                             And A.create_time > B.create_time
                           )
From Table as A

(※実行・期待された結果は取得できるが、時間がかかってしまう)

X,Yの組み合わせごとに、当該データより(create_timeが)古いデータの計数を行いたいです。
組み合わせ数・行数が多いため、クエリが終わらない状況となってしまっています。

上記のMySQLバージョンだと、With句・ウィンドウ関数が使えないみたいで…
サブクエリ部分をCaseで分岐させてテーブル1周で実施できないか試したりしたのですが、うまく行きませんでした。

不足・不明点等あれば、ご指摘いただけますと幸いです。
以上、何卒宜しくお願いいたします。

Comments

[yambejp]

テーブルの具体的な構成とサンプルを少々、その期待する結果を表記されるとよいかと
それと「count(X=1 and Y=2)」が何をしたいかわかりません
この場合件数を数えたいならsumです

[wwwww]

記載に不足あり、失礼いたしました。ご回答、ありがとうございます。
(別途ご記載のサンプルについても、ありがとうございます。)

サンプル・期待結果について、質問文にて追記いたしました。

＞「count(X=1 and Y=2)」が何をしたいかわかりません
＞件数を数えたいならsumです
X=1、Y=2に該当する場合の件数を集計したいです。
countでも取れていたのですが、sumで良い旨、知りませんでした。ご教示ありがとうございます。

Answer 1

先ずは、(create_time,X,Y)のインデックスを追加してみて下さい。

Comments

[wwwww]

ありがとうございます。インデックス追加してみたら、約50倍早くなりました。笑
初歩的な質問にてお手間頂いてしまい、失礼いたしました。

Answer 2

一応サンプル
(w,zは重要ではないので省略しidをつけておきました）

create table tbl(id int primary key auto_increment,x int,y int,create_time datetime,
index(x,y,create_time));
insert into tbl(x,y,create_time) values
(1,2,'2019-11-20 01:00'),
(1,2,'2019-11-20 01:10'),
(1,2,'2019-11-20 01:20'),
(1,2,'2019-11-20 01:20'),
(1,2,'2019-11-20 01:20'),
(1,2,'2019-11-20 01:30'),
(1,3,'2019-11-20 01:00'),
(1,3,'2019-11-20 01:10'),
(1,3,'2019-11-20 01:10'),
(1,3,'2019-11-20 01:10'),
(1,3,'2019-11-20 01:20'),
(1,3,'2019-11-20 01:30');

select a.*,
(select sum(x=1 and y=2) from tbl where a.create_time>create_time) as cnt
from tbl as a;

確認

「●期待する結果」がなぜそういう結果になるかわかりません

自分のcreate_timeより前のx=1かつy=2の件数だったらこうなりませんか？

x	y	create_time	count
0	0	2019-11-20 01:00:00	NULL
0	1	2019-11-20 01:10:00	0
0	2	2019-11-20 01:20:00	0
0	1	2019-11-20 01:30:00	0
1	2	2019-11-20 01:40:00	0
1	1	2019-11-20 01:50:00	1
2	0	2019-11-20 02:00:00	1
0	1	2019-11-20 03:50:00	1
0	1	2019-11-20 05:50:00	1

修正版

create table tbl(id int primary key auto_increment,name varchar(10),x int,y int,create_time datetime,
index(name,x,y,create_time));
insert into tbl(name,x,y,create_time) values
('A',0,0,'2019-11-20 01:00'),
('A',0,1,'2019-11-20 01:10'),
('B',0,1,'2019-11-20 01:20'),
('A',0,1,'2019-11-20 01:30'),
('A',1,1,'2019-11-20 01:40'),
('B',1,1,'2019-11-20 01:50'),
('A',0,1,'2019-11-20 02:00'),
('A',0,1,'2019-11-20 03:50'),
('B',0,1,'2019-11-20 05:50');

select a.*,
coalesce((select sum(x=0 and y=0) from tbl where a.create_time>create_time and a.name=name),0) as cnt_0_0,
coalesce((select sum(x=0 and y=1) from tbl where a.create_time>create_time and a.name=name),0) as cnt_0_1,
coalesce((select sum(x=1 and y=0) from tbl where a.create_time>create_time and a.name=name),0) as cnt_1_0,
coalesce((select sum(x=1 and y=1) from tbl where a.create_time>create_time and a.name=name),0) as cnt_1_1
from tbl as a
order by id;

• 結果

id	name	x	y	create_time	cnt_0_0	cnt_0_1	cnt_1_0	cnt_1_1
1	A	0	0	2019-11-20 01:00:00	0	0	0	0
2	A	0	1	2019-11-20 01:10:00	1	0	0	0
3	B	0	1	2019-11-20 01:20:00	0	0	0	0
4	A	0	1	2019-11-20 01:30:00	1	1	0	0
5	A	1	1	2019-11-20 01:40:00	1	2	0	0
6	B	1	1	2019-11-20 01:50:00	0	1	0	0
7	A	0	1	2019-11-20 02:00:00	1	2	0	1
8	A	0	1	2019-11-20 03:50:00	1	3	0	1
9	B	0	1	2019-11-20 05:50:00	0	1	0	1

Comments

[yambejp]

追記分について確認をかいておきました？
ロジックを説明したほうが良いと思います

[wwwww]

ご指摘いただき、ありがとうございます。大変失礼いたしました。
私の初期の記載に大幅に誤りがあったため、修正させていただきました。申し訳ありません。。

[yambejp]

追記しました、内容的にあってますか？

[wwwww]

数回にわたり、ありがとうございます。
ご記載の内容で欲しい結果取れますこと、確認できました
お手数おかけいたしました。

Answer 3

う～ん自信なしですが。
テーブル1周で済むように工夫してみました。
もっといい方法があるような気がします。

SELECT
create_time,W,X,Y,Z,
@i,
(CASE WHEN X=1 and Y=2 THEN @i := @i + 1 ELSE @i END)AS dummy
FROM
(SELECT @i := 0) AS dummy,
Table
ORDER BY create_time;

@iが求めている値です。dummyも結果に出てしまいますが無視して下さい。

Comments

[wwwww]

ご教示ありがとうございます。恥ずかしながら、SQLで変数を使えると知りませんでした。
改めて勉強しておきたいと思っています。