常連

自己紹介

■環境
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|
```
```MySQL
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周で実施できないか試したりしたのですが、うまく行きませんでした。

不足・不明点等あれば、ご指摘いただけますと幸いです。
以上、何卒宜しくお願いいたします。

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

MySQLのトランザクション中の挙動について理解できていない所があり、投稿致しました。
下記に、「実行環境」、「使用テーブル」、「実行結果」、「私が期待していた結果」、「PHPプログラム」を示します。

PHPプログラム実行後、「tanaka」さんが二つ登録されています（下記「実行結果」ご参照）
これは下記の「私が期待していた結果」に反していて不思議に感じています。また何故このような結果になるのか分からずに悩んでいます。

よろしければ、こういった結果になる理由や、「私が期待していた結果」にするための方法について教えて頂ければ嬉しいです。

---

**実行環境**
MySQLバージョン：5.5.38
PHPバージョン：5.5.29
レンタルサーバ：kagoya
　

**使用テーブル（testテーブル）**
※engine：InnoDB、isolation：REPEATABLE-READ、id:auto_increment
|id|name|
|:--|:--:|
|1|sato|
|2|suzuki|



**実行結果（testテーブル）**
|id|name|
|:--|:--:|
|1|sato|
|3|tanaka|
|4|tanaka|

**私が期待していた結果（testテーブル）**
|id|name|
|:--|:--:|
|1|sato|
|3|tanaka|  
 
**PHPプログラム**

```PHP
<?php
session_start();
require_once('config.php');
require_once('functions.php');

// DB接続
$dbh = connectDb();

// トランザクション開始
$dbh->beginTransaction();

echo 'begin transaction：' . '<br>';

try {
    
    $sql = "delete from test 
        where id = :id";
    $stmt = $dbh->prepare($sql);
    $ret = $stmt->execute(array(
        ":id" => 2
    ));

    $sql = "select * from test";
    $stmt = $dbh->query($sql);
    $test = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo 'after delete：';
    var_dump($test);
    
    
    $sql = "insert into test (name) values (:name)";
    $stmt = $dbh->prepare($sql);
    $ret = $stmt->execute(array(
        ":name" => 'tanaka'
    ));

    // commit
    $dbh->commit();

    unset($dbh);

} catch (PDOException $e) {
    $dbh->rollBack();
    die($e->getMessage());
}

?>
```

※不思議なことにローカル環境（Windows、MySQL5.6.24、PHP5.6.8）では「私が期待していた結果」になっています。ローカル環境とKagoyaサーバ環境で挙動が違っていて更に悩んでいます。