SELECT a.date, a.c, b.foo, a.foo

    FROM

        (SELECT date,c,SUM(foo) as foo

         FROM raw_b

         WHERE date >= 20211231

         GROUP BY date,c

        ) as b,

        (SELECT date, c, SUM(foo) as foo

         FROM raw_a

         WHERE date >= 20211231

         GROUP BY date,c

        ) as a where a.date = b.date AND a.c = b.c

Schema:

CREATE TABLE `raw_a` (

  `date` varchar(100) COLLATE latin1_general_cs DEFAULT NULL,

  `v` varchar(100) COLLATE latin1_general_cs DEFAULT NULL,

  `c` char(5) COLLATE latin1_general_cs DEFAULT NULL,

  `foo` bigint(20) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC

CREATE TABLE `raw_b` (

  `date` int(11) NOT NULL,

  `v` varchar(10) COLLATE latin1_general_cs NOT NULL,

  `t` varchar(512) COLLATE latin1_general_cs NOT NULL,

  `c` varchar(50) COLLATE latin1_general_cs NOT NULL,

  `o` varchar(10) COLLATE latin1_general_cs NOT NULL,

  `sh` varchar(10) COLLATE latin1_general_cs NOT NULL,

  `p` char(5) COLLATE latin1_general_cs NOT NULL,

  `s` varchar(20) COLLATE latin1_general_cs NOT NULL,

  `foo` bigint(20) DEFAULT NULL,

  PRIMARY KEY (`date`,`v`,`t`,`c`,`o`,`sh`,`p`,`s`),

  KEY `date` (`date`,`c`,`v`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs ROW_FORMAT=DYNAMIC

(root:localhost) [test]> select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);        

+------+------------------+

| id   | email            |

+------+------------------+

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

| 2070 | 2559test@test.ee |

+------+------------------+

8 rows in set (0.001 sec)

(root:localhost) [test]> explain select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);

+------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+

| id   | select_type | table      | type | possible_keys | key        | key_len | ref       | rows | Extra                                        |

+------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+

|    1 | PRIMARY     | c          | ref  | id,deleted    | deleted    | 4       | const     | 1    | Using where; Using index                     |

|    1 | PRIMARY     | d          | ref  | company_id    | company_id | 4       | test.c.id | 1    | Using index                                  |

|    1 | PRIMARY     | <derived2> | ref  | key0          | key0       | 4       | test.c.id | 2    |                                              |

|    2 | DERIVED     | cpt        | ALL  | company_id    | NULL       | NULL    | NULL      | 1    | Using where; Using temporary; Using filesort |

+------+-------------+------------+------+---------------+------------+---------+-----------+------+----------------------------------------------+

4 rows in set (0.000 sec)

(root:localhost) [test]> select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);        

+------+-------+

| id   | email |

+------+-------+

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

| 2070 |       |

+------+-------+

8 rows in set (0.001 sec)

(root:localhost) [test]> explain select c.id, cpe.email from company c inner join member_data d ON d.company_id=c.id inner join (  select company_id, email from company_contact_person cpt where reporting_person=1  group by company_id  ) cpe ON cpe.company_id=c.id where c.deleted=0 and c.id in (1,2070);

+------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+

| id   | select_type     | table      | type  | possible_keys | key        | key_len | ref       | rows | Extra                    |

+------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+

|    1 | PRIMARY         | c          | range | id,deleted    | deleted    | 8       | NULL      | 2    | Using where; Using index |

|    1 | PRIMARY         | d          | ref   | company_id    | company_id | 4       | test.c.id | 1    | Using index              |

|    1 | PRIMARY         | <derived2> | ref   | key0          | key0       | 4       | test.c.id | 2    |                          |

|    2 | LATERAL DERIVED | cpt        | ref   | company_id    | company_id | 4       | test.c.id | 1    | Using index condition    |

+------+-----------------+------------+-------+---------------+------------+---------+-----------+------+--------------------------+

4 rows in set (0.000 sec)

--source include/have_innodb.inc

CREATE TABLE t1 ( id int, UNIQUE KEY id (id) ) ENGINE=InnoDB;

INSERT INTO t1 VALUES (-1),(2070),(4826),(4827),(4828),(4829),(4830),(4831),(4832),(4833),(4834),(4835),(4836),(4837),(4838),(4839),(4840),(4841),(4842),(4843),(4844),(4845),(4846),(4847),(4848),(4849),(4850),(4851),(4852),(4853),(4854),(4855),(4856),(4857),(4858),(4859),(4860),(4861),(4862),(4863),(4864),(4865),(4866),(4867),(4868),(4869),(4870),(4871),(4872),(4873),(4874),(4875),(4876);

CREATE TABLE t2 ( id int, deleted int(1), t1_id int, email varchar(255), reporting_person int(1),

  UNIQUE KEY id (id),

  KEY t1_id (t1_id)

) ENGINE=InnoDB;

INSERT INTO t2 VALUES (1,0,2064,'1test@test.ee',1),(2,1626095588,2066,'2test@test.ee',1),(3,0,2066,'3test@test.ee',1),(4,0,2068,'4test@test.ee',1),(5,0,2068,'5test@test.ee',1),(6,0,2069,'6test@test.ee',1),(7,0,2070,'',0),(8,0,2070,'',0),(9,0,2071,'',0),(10,0,2071,'',0),(11,0,2072,'',0),(12,0,2072,'',0),(13,0,2072,'13test@test.ee',1),(14,0,2073,'14test@test.ee',1),(15,0,2074,'15test@test.ee',1),(16,0,2075,'16test@test.ee',1),(17,0,2075,'',0),(18,0,2075,'',0),(19,0,2076,'19test@test.ee',1),(20,0,2077,'',0),(21,0,2078,'21test@test.ee',1),(22,0,2078,'22test@test.ee',1);

CREATE TABLE t3 ( id int, deleted int, t1_id int, YEAR int(4), quarter int(1),

  UNIQUE KEY id (id),

  KEY t1_id (t1_id,year,quarter)

) ENGINE=InnoDB;

INSERT INTO t3 VALUES (1,0,3885,2020,1),(2,0,2064,2020,1),(3,1611670734,2225,2020,1),(4,0,2070,2020,1),(5,1611055981,2095,2020,1),(6,1610970096,2102,2020,1),(7,0,3974,2020,1),(153,1609851928,3892,2020,2),(154,0,3885,2020,2),(155,0,2064,2020,2),(156,1611670717,2225,2020,2),(157,0,2070,2020,2),(317,0,2257,2020,2),(318,0,3885,2020,3),(319,0,2064,2020,3),(320,1611670709,2225,2020,3),(321,0,2070,2020,3),(322,0,2095,2020,3),(323,0,2102,2020,3),(324,0,3974,2020,3),(325,0,3886,2020,3),(326,1609939963,2104,2020,3),(327,0,3887,2020,3),(328,0,3888,2020,3),(329,0,2148,2020,3),(330,0,3889,2020,3),(331,0,3890,2020,3),(332,0,2179,2020,3),(333,0,2115,2020,3),(334,0,2193,2020,3),(335,0,2213,2020,3),(336,0,3891,2020,3),(337,1609851955,3892,2020,3),(338,1610447706,2232,2020,3),(339,0,2235,2020,3),(340,0,2237,2020,3),(341,0,3972,2020,3),(342,1610449357,2242,2020,3),(343,0,3893,2020,3),(344,0,2257,2020,3),(345,0,3951,2020,3),(346,0,3894,2020,3),(347,0,3912,2020,3),(348,0,3895,2020,3),(349,0,2301,2020,3),(350,0,2304,2020,3),(351,0,3896,2020,3);

SELECT t1.id, tx.email

FROM t1 

JOIN t3  ON t3.t1_id = t1.id

JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

WHERE t1.id in(1, 2070);

set  optimizer_switch='split_materialized=off';

SELECT t1.id, tx.email

FROM t1 

JOIN t3  ON t3.t1_id = t1.id

JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

WHERE t1.id in(1, 2070);

MariaDB [test]> SELECT t1.id, tx.email

    -> FROM t1 

    -> JOIN t3  ON t3.t1_id = t1.id

    -> JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

    -> WHERE t1.id in(1, 2070);

+------+-------+

| id   | email |

+------+-------+

| 2070 |       |

| 2070 |       |

| 2070 |       |

+------+-------+

3 rows in set (0.002 sec)

MariaDB [test]> set  optimizer_switch='split_materialized=off';

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT t1.id, tx.email

    -> FROM t1 

    -> JOIN t3  ON t3.t1_id = t1.id

    -> JOIN (SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

    -> WHERE t1.id in(1, 2070);

Empty set (0.002 sec)

(SELECT t1_id, email FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx

--source include/have_innodb.inc

CREATE TABLE raw_a (

  date varchar(100),

  v varchar(100),

  c char(5),

  foo bigint(20)

) ENGINE=InnoDB;

INSERT INTO raw_a VALUES ('20211231','A','bh',1),('20211231','B','bh',0),('20211231','C','bh',0),('20211231','D','bh',0),('20211231','E','bh',0),('20211231','G','bh',0),('20211231','H','bh',0),('20211231','I','bh',3),('20211231','J','bh',0),('20211231','K','bh',0),('20211231','A','bl',9),('20211231','B','bl',0),('20211231','C','bl',0),('20211231','D','bl',0),('20211231','E','bl',0),('20211231','G','bl',0),('20211231','H','bl',0),('20211231','I','bl',7),('20211231','J','bl',0),('20211231','K','bl',0),('20211231','M','bl',0),('20211231','N','bl',3),('20211231','R','bl',9),('20211231','S','bl',0),('20211231','T','bl',0),('20211231','U','bl',0),('20211231','V','bl',0);

CREATE TABLE raw_b (

  date int(11) NOT NULL,

  v varchar(50),

  t varchar(512),

  c varchar(50),

  o varchar(10),

  sh varchar(10),

  p char(5),

  s varchar(20),

  foo bigint(20),

  PRIMARY KEY (date,v,t,c,o,sh,p,s),

  KEY date (date,c,v)

) ENGINE=InnoDB;

INSERT INTO raw_b VALUES (20211231,'A','a','bl','a','f','0','[0-5]',2),(20211231,'A','a','bl','a','f','0','[6-9)',2),(20211231,'A','b','bl','a','f','0','[0-5]',2),(20211231,'A','b','bl','a','f','0','[6-9)',2),(20211231,'A','c','bh','a','f','0','[0-5]',2),(20211231,'A','c','bh','a','f','0','[10-15)',2),(20211231,'A','c','bh','a','f','0','[15-20)',2),(20211231,'A','c','bh','a','f','0','[20-30)',2),(20211231,'A','c','bh','a','f','0','[6-9)',2),(20211231,'A','c','bl','a','f','0','[0-5]',2),(20211231,'A','c','bl','a','f','0','[20-30)',2),(20211231,'A','c','bl','a','f','0','[6-9)',2),(20211231,'A','cc','bl','a','f','0','[0-5]',2),(20211231,'A','cc','bl','a','f','0','[10-15)',2),(20211231,'A','cc','bl','a','f','0','[15-20)',2),(20211231,'A','cc','bl','a','f','0','[6-9)',2),(20211231,'A','cc','bl','a','LO','0','[0-5]',2),(20211231,'A','ccc','bl','a','f','0','[0-5]',2),(20211231,'A','d','bh','a','f','0','[0-5]',2),(20211231,'A','d','bh','a','f','0','[10-15)',2),(20211231,'A','d','bh','a','f','0','[20-30)',2),(20211231,'A','d','bh','a','f','0','[6-9)',2),(20211231,'A','e','bh','a','f','0','[0-5]',2),(20211231,'A','e','bh','a','f','0','[10-15)',2),(20211231,'A','e','bh','a','f','0','[20-30)',2),(20211231,'A','e','bh','a','f','0','[6-9)',2),(20211231,'A','f','bh','a','f','0','[6-9)',2),(20211231,'A','g','bh','a','f','0','[0-5]',2),(20211231,'A','g','bh','a','f','0','[10-15)',2),(20211231,'A','g','bh','a','f','0','[6-9)',2),(20211231,'A','h','bh','a','f','0','[0-5]',2),(20211231,'A','h','bh','a','f','0','[10-15)',2),(20211231,'A','h','bh','a','f','0','[15-20)',2),(20211231,'A','h','bh','a','f','0','[20-30)',2),(20211231,'A','h','bh','a','f','0','[30-inf)',2),(20211231,'A','h','bh','a','f','0','[6-9)',2),(20211231,'A','h','bh','a','f','1-4','[10-15)',2),(20211231,'A','h','bh','a','f','<0','[0-5]',2),(20211231,'A','i','bh','a','f','0','[0-5]',2),(20211231,'A','i','bh','a','f','0','[20-30)',2),(20211231,'A','i','bh','a','f','0','[6-9)',2),(20211231,'A','j','bh','a','f','0','[0-5]',2),(20211231,'A','j','bh','a','f','0','[6-9)',2),(20211231,'A','k','bh','a','f','0','[0-5]',2),(20211231,'A','k','bh','a','f','0','[10-15)',2),(20211231,'A','k','bh','a','f','0','[15-20)',2),(20211231,'A','k','bh','a','f','0','[20-30)',2),(20211231,'A','k','bh','a','f','0','[30-inf)',2),(20211231,'A','k','bh','a','f','0','[6-9)',2),(20211231,'A','l','bh','a','f','0','[0-5]',2),(20211231,'A','m','bh','a','f','0','[0-5]',2),(20211231,'A','m','bh','a','f','0','[10-15)',2),(20211231,'A','m','bh','a','f','0','[15-20)',2),(20211231,'A','m','bh','a','f','0','[20-30)',2),(20211231,'A','m','bh','a','f','0','[6-9)',2),(20211231,'A','m','bh','a','f','>4','[10-15)',2),(20211231,'A','n','bh','a','f','0','[0-5]',2),(20211231,'A','n','bl','a','f','0','[0-5]',2),(20211231,'A','o','bh','a','f','0','[0-5]',2),(20211231,'A','o','bh','a','f','0','[6-9)',2),(20211231,'A','p','bh','a','f','0','[0-5]',2),(20211231,'A','p','bh','a','f','0','[10-15)',2),(20211231,'A','p','bh','a','f','0','[15-20)',2),(20211231,'A','p','bh','a','f','0','[6-9)',2),(20211231,'A','p','bh','a','f','<0','[0-5]',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[0-5]',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[10-15)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[15-20)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[20-30)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[30-inf)',2),(20211231,'Afdaskjlhfaslkjfhasdklfhdskla','j','bh','a','f','0','[6-9)',2);

SELECT a.date, a.c, b.foo, a.foo

    FROM

        (SELECT date,c,SUM(foo) as foo

         FROM raw_b

         WHERE date >= 20211231

         GROUP BY date,c

        ) as b,

        (SELECT date, c, SUM(foo) as foo

         FROM raw_a

         WHERE date >= 20211231

         GROUP BY date,c

        ) as a where a.date = b.date AND a.c = b.c;

 set optimizer_switch="split_materialized=off";

SELECT a.date, a.c, b.foo, a.foo

    FROM

        (SELECT date,c,SUM(foo) as foo

         FROM raw_b

         WHERE date >= 20211231

         GROUP BY date,c

        ) as b,

        (SELECT date, c, SUM(foo) as foo

         FROM raw_a

         WHERE date >= 20211231

         GROUP BY date,c

        ) as a where a.date = b.date AND a.c = b.c;

SELECT t1.id

FROM t1 

JOIN t3  ON t3.t1_id = t1.id

JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx ON tx.t1_id = t1.id

WHERE t1.id in(1, 2070);

MariaDB [test]> EXPLAIN FORMAT=JSON

    -> SELECT t1.id

    -> FROM t1 

    -> JOIN t3  ON t3.t1_id = t1.id

    -> JOIN (SELECT t1_id FROM t2 WHERE reporting_person = 1 GROUP BY t1_id) tx 

    -> ON tx.t1_id = t1.id

    -> WHERE t1.id in(1, 2070);

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "t3",

      "access_type": "index",

      "possible_keys": ["t1_id"],

      "key": "t1_id",

      "key_length": "15",

      "used_key_parts": ["t1_id", "YEAR", "quarter"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "t3.t1_id in (1,2070) and t3.t1_id is not null and t3.t1_id is not null",

      "using_index": true

    },

    "table": {

      "table_name": "t1",

      "access_type": "eq_ref",

      "possible_keys": ["id"],

      "key": "id",

      "key_length": "5",

      "used_key_parts": ["id"],

      "ref": ["test.t3.t1_id"],

      "rows": 1,

      "filtered": 100,

      "using_index": true

    },

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "5",

      "used_key_parts": ["t1_id"],

      "ref": ["test.t3.t1_id"],

      "rows": 2,

      "filtered": 100,

      "materialized": {

        "lateral": 1,

        "query_block": {

          "select_id": 2,

          "outer_ref_condition": "t1.`id` is not null",

          "table": {

            "table_name": "t2",

            "access_type": "ref",

            "possible_keys": ["t1_id"],

            "key": "t1_id",

            "key_length": "5",

            "used_key_parts": ["t1_id"],

            "ref": ["test.t1.id"],

            "rows": 1,

            "filtered": 100,

            "index_condition": "t2.t1_id in (1,2070)"

          }

        }

      }

    }

  }

} |

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.001 sec)

MariaDB [test]> EXPLAIN FORMAT=JSON

    -> SELECT COUNT(*)

    -> FROM project_time_lines_dates ptld

    -> 

    -> INNER JOIN date_revision dr

    ->     ON dr.id = ptld.date_revision_id

    -> 

    -> INNER JOIN (

    ->     SELECT

    ->         ptld.project_id AS project_id,

    ->         ptld.project_type_id AS project_type_id,

    ->         ptld.leaf_component_id AS leaf_component_id,

    ->         dr.date_id AS date_id,

    ->          MAX(dr.order_idx) AS max_order

    ->     FROM project_time_lines_dates ptld

    ->     INNER JOIN date_revision dr

    ->         ON dr.id = ptld.date_revision_id

    ->     WHERE dr.date_id IN (

    ->         SELECT DISTINCT dr.date_id

    ->         FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr

    ->         INNER JOIN date_revision dr

    ->             ON dr.id = tstrdr.start_date_revision_id

    ->         WHERE tstrdr.offered_for_overdue

    ->     )

    ->     

    ->     GROUP BY

    ->         ptld.project_id,

    ->         ptld.project_type_id,

    ->         ptld.leaf_component_id,

    ->         dr.date_id

    -> ) max_anticipated

    ->     ON max_anticipated.project_id = ptld.project_id

    ->     AND max_anticipated.project_type_id = ptld.project_type_id

    ->     AND max_anticipated.date_id = dr.date_id

    ->     AND max_anticipated.max_order = dr.order_idx

    -> 

    -> WHERE dr.is_anticipated = TRUE

    ->     AND ptld.project_id = 5896

    -> ;

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    8

Current database: test

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "ptld",

      "access_type": "ref",

      "possible_keys": [

        "project_id_project_type_id_leaf_component_id_date_revision_id",

        "project_id",

        "project_id_2"

      ],

      "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

      "key_length": "4",

      "used_key_parts": ["project_id"],

      "ref": ["const"],

      "rows": 34,

      "filtered": 100,

      "using_index": true

    },

    "table": {

      "table_name": "dr",

      "access_type": "eq_ref",

      "possible_keys": ["PRIMARY", "date_id"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "ref": ["test.ptld.date_revision_id"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "dr.is_anticipated = 1"

    },

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "4",

      "used_key_parts": ["project_type_id"],

      "ref": ["test.ptld.project_type_id"],

      "rows": 2,

      "filtered": 100,

      "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx",

      "materialized": {

        "lateral": 1,

        "query_block": {

          "select_id": 2,

          "filesort": {

            "sort_key": "ptld.leaf_component_id",

            "temporary_table": {

              "table": {

                "table_name": "dr",

                "access_type": "ref",

                "possible_keys": ["PRIMARY", "date_id"],

                "key": "date_id",

                "key_length": "4",

                "used_key_parts": ["date_id"],

                "ref": ["test.dr.date_id"],

                "rows": 1,

                "filtered": 100

              },

              "duplicates_removal": {

                "table": {

                  "table_name": "dr",

                  "access_type": "ref",

                  "possible_keys": ["PRIMARY", "date_id"],

                  "key": "date_id",

                  "key_length": "4",

                  "used_key_parts": ["date_id"],

                  "ref": ["test.dr.date_id"],

                  "rows": 1,

                  "filtered": 100,

                  "using_index": true

                },

                "table": {

                  "table_name": "tstrdr",

                  "access_type": "ref",

                  "possible_keys": ["start_date_revision_id"],

                  "key": "start_date_revision_id",

                  "key_length": "5",

                  "used_key_parts": ["start_date_revision_id"],

                  "ref": ["test.dr.id"],

                  "rows": 4,

                  "filtered": 100,

                  "attached_condition": "tstrdr.offered_for_overdue <> 0"

                }

              },

              "table": {

                "table_name": "ptld",

                "access_type": "ref",

                "possible_keys": [

                  "project_id_project_type_id_leaf_component_id_date_revision_id",

                  "project_id",

                  "project_id_2"

                ],

                "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

                "key_length": "4",

                "used_key_parts": ["project_id"],

                "ref": ["const"],

                "rows": 34,

                "filtered": 100,

                "using_index": true

              }

            }

          }

        }

      }

    }

  }

} |

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [test]> EXPLAIN FORMAT=JSON

    -> SELECT COUNT(*)

    -> FROM project_time_lines_dates ptld

    -> 

    -> INNER JOIN date_revision dr

    ->     ON dr.id = ptld.date_revision_id

    -> 

    -> INNER JOIN (

    ->     SELECT

    ->         ptld.project_id AS project_id,

    ->         ptld.project_type_id AS project_type_id,

    ->         ptld.leaf_component_id AS leaf_component_id,

    ->         dr.date_id AS date_id,

    ->          MAX(dr.order_idx) AS max_order

    ->     FROM project_time_lines_dates ptld

    ->     INNER JOIN date_revision dr

    ->         ON dr.id = ptld.date_revision_id

    ->     WHERE dr.date_id IN (

    ->         SELECT DISTINCT dr.date_id

    ->         FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr

    ->         INNER JOIN date_revision dr

    ->             ON dr.id = tstrdr.start_date_revision_id

    ->         WHERE tstrdr.offered_for_overdue

    ->     )

    ->     

    ->     GROUP BY

    ->         ptld.project_id,

    ->         ptld.project_type_id,

    ->         ptld.leaf_component_id,

    ->         dr.date_id

    -> ) max_anticipated

    ->     ON max_anticipated.project_id = ptld.project_id

    ->     AND max_anticipated.project_type_id = ptld.project_type_id

    ->     AND max_anticipated.date_id = dr.date_id

    ->     AND max_anticipated.max_order = dr.order_idx

    -> 

    -> WHERE dr.is_anticipated = TRUE

    ->     AND ptld.project_id = 5896

    -> ;

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| {

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "ptld",

      "access_type": "ref",

      "possible_keys": [

        "project_id_project_type_id_leaf_component_id_date_revision_id",

        "project_id",

        "project_id_2"

      ],

      "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

      "key_length": "4",

      "used_key_parts": ["project_id"],

      "ref": ["const"],

      "rows": 34,

      "filtered": 100,

      "using_index": true

    },

    "table": {

      "table_name": "dr",

      "access_type": "eq_ref",

      "possible_keys": ["PRIMARY", "date_id"],

      "key": "PRIMARY",

      "key_length": "4",

      "used_key_parts": ["id"],

      "ref": ["test.ptld.date_revision_id"],

      "rows": 1,

      "filtered": 100,

      "attached_condition": "dr.is_anticipated = 1"

    },

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "4",

      "used_key_parts": ["project_type_id"],

      "ref": ["test.ptld.project_type_id"],

      "rows": 2,

      "filtered": 100,

      "attached_condition": "max_anticipated.project_id = 5896 and max_anticipated.date_id = dr.date_id and max_anticipated.max_order = dr.order_idx",

      "materialized": {

        "lateral": 1,

        "query_block": {

          "select_id": 2,

          "filesort": {

            "sort_key": "ptld.leaf_component_id",

            "temporary_table": {

              "table": {

                "table_name": "dr",

                "access_type": "ref",

                "possible_keys": ["PRIMARY", "date_id"],

                "key": "date_id",

                "key_length": "4",

                "used_key_parts": ["date_id"],

                "ref": ["test.dr.date_id"],

                "rows": 1,

                "filtered": 100

              },

              "duplicates_removal": {

                "table": {

                  "table_name": "dr",

                  "access_type": "ref",

                  "possible_keys": ["PRIMARY", "date_id"],

                  "key": "date_id",

                  "key_length": "4",

                  "used_key_parts": ["date_id"],

                  "ref": ["test.dr.date_id"],

                  "rows": 1,

                  "filtered": 100,

                  "using_index": true

                },

                "table": {

                  "table_name": "tstrdr",

                  "access_type": "ref",

                  "possible_keys": ["start_date_revision_id"],

                  "key": "start_date_revision_id",

                  "key_length": "5",

                  "used_key_parts": ["start_date_revision_id"],

                  "ref": ["test.dr.id"],

                  "rows": 4,

                  "filtered": 100,

                  "attached_condition": "tstrdr.offered_for_overdue <> 0"

                }

              },

              "table": {

                "table_name": "ptld",

                "access_type": "ref",

                "possible_keys": [

                  "project_id_project_type_id_leaf_component_id_date_revision_id",

                  "project_id",

                  "project_id_2"

                ],

                "key": "project_id_project_type_id_leaf_component_id_date_revision_id",

                "key_length": "4",

                "used_key_parts": ["project_id"],

                "ref": ["const"],

                "rows": 34,

                "filtered": 100,

                "attached_condition": "ptld.date_revision_id = dr.`id`",

                "using_index": true

              }

            }

          }

        }

      }

    }

  }

} |

+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [test]> SELECT COUNT(*)

    -> FROM project_time_lines_dates ptld

    -> 

    -> INNER JOIN date_revision dr

    ->     ON dr.id = ptld.date_revision_id

    -> 

    -> INNER JOIN (

    ->     SELECT

    ->         ptld.project_id AS project_id,

    ->         ptld.project_type_id AS project_type_id,

    ->         ptld.leaf_component_id AS leaf_component_id,

    ->         dr.date_id AS date_id,

    ->          MAX(dr.order_idx) AS max_order

    ->     FROM project_time_lines_dates ptld

    ->     INNER JOIN date_revision dr

    ->         ON dr.id = ptld.date_revision_id

    ->     WHERE dr.date_id IN (

    ->         SELECT DISTINCT dr.date_id

    ->         FROM time_sheet_activities_time_sheet_roles_date_revisions tstrdr

    ->         INNER JOIN date_revision dr

    ->             ON dr.id = tstrdr.start_date_revision_id

    ->         WHERE tstrdr.offered_for_overdue

    ->     )

    ->     

    ->     GROUP BY

    ->         ptld.project_id,

    ->         ptld.project_type_id,

    ->         ptld.leaf_component_id,

    ->         dr.date_id

    -> ) max_anticipated

    ->     ON max_anticipated.project_id = ptld.project_id

    ->     AND max_anticipated.project_type_id = ptld.project_type_id

    ->     AND max_anticipated.date_id = dr.date_id

    ->     AND max_anticipated.max_order = dr.order_idx

    -> 

    -> WHERE dr.is_anticipated = TRUE

    ->     AND ptld.project_id = 5896

    -> ;

+----------+

| COUNT(*) |

+----------+

|        2 |

+----------+