select * FROM COMPLETED_TXN_COMPONENTS tc INNER JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) highestWriteId FROM COMPLETED_TXN_COMPONENTS GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c ON tc.CTC_DATABASE = c.CTC_DATABASE AND tc.CTC_TABLE = c.CTC_TABLE AND tc.CTC_PARTITION <=> c.CTC_PARTITION LEFT JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) updateWriteId FROM COMPLETED_TXN_COMPONENTS WHERE CTC_UPDATE_DELETE = 'Y' GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c2 ON tc.CTC_DATABASE = c2.CTC_DATABASE AND tc.CTC_TABLE = c2.CTC_TABLE AND tc.CTC_PARTITION <=> c2.CTC_PARTITION WHERE tc.CTC_WRITEID < c.highestWriteId AND NOT tc.CTC_WRITEID <=> c2.updateWriteId; MariaDB [test]> set optimizer_switch='split_materialized=off'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> MariaDB [test]> analyze select * FROM COMPLETED_TXN_COMPONENTS tc INNER JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) highestWriteId FROM COMPLETED_TXN_COMPONENTS GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c ON tc.CTC_DATABASE = c.CTC_DATABASE AND tc.CTC_TABLE = c.CTC_TABLE AND tc.CTC_PARTITION <=> c.CTC_PARTITION LEFT JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) updateWriteId FROM COMPLETED_TXN_COMPONENTS WHERE CTC_UPDATE_DELETE = 'Y' GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c2 ON tc.CTC_DATABASE = c2.CTC_DATABASE AND tc.CTC_TABLE = c2.CTC_TABLE AND tc.CTC_PARTITION <=> c2.CTC_PARTITION WHERE tc.CTC_WRITEID < c.highestWriteId AND NOT tc.CTC_WRITEID <=> c2.updateWriteId; +------+-------------+--------------------------+-------+------------------------------+------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+--------------------------+-------+------------------------------+------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 145594 | 140069.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | tc | ref | COMPLETED_TXN_COMPONENTS_IDX | COMPLETED_TXN_COMPONENTS_IDX | 1159 | c.CTC_DATABASE,c.CTC_TABLE,c.CTC_PARTITION | 1 | 1.01 | 100.00 | 0.68 | Using index condition; Using where | | 1 | PRIMARY | | ref | key0 | key0 | 390 | c.CTC_DATABASE,c.CTC_TABLE | 10 | 248.84 | 100.00 | 0.40 | Using where | | 3 | DERIVED | COMPLETED_TXN_COMPONENTS | index | NULL | COMPLETED_TXN_COMPONENTS_IDX | 1159 | NULL | 145594 | 141021.00 | 100.00 | 71.18 | Using where | | 2 | DERIVED | COMPLETED_TXN_COMPONENTS | index | NULL | COMPLETED_TXN_COMPONENTS_IDX | 1159 | NULL | 145594 | 141021.00 | 100.00 | 100.00 | | +------+-------------+--------------------------+-------+------------------------------+------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+------------------------------------+ 5 rows in set (22.867 sec) MariaDB [test]> analyze select * FROM COMPLETED_TXN_COMPONENTS tc INNER JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) highestWriteId FROM COMPLETED_TXN_COMPONENTS GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c ON tc.CTC_DATABASE = c.CTC_DATABASE AND tc.CTC_TABLE = c.CTC_TABLE AND tc.CTC_PARTITION <=> c.CTC_PARTITION LEFT JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) updateWriteId FROM COMPLETED_TXN_COMPONENTS WHERE CTC_UPDATE_DELETE = 'Y' GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c2 ON tc.CTC_DATABASE = c2.CTC_DATABASE AND tc.CTC_TABLE = c2.CTC_TABLE AND tc.CTC_PARTITION <=> c2.CTC_PARTITION WHERE tc.CTC_WRITEID < c.highestWriteId AND NOT tc.CTC_WRITEID <=> c2.updateWriteId; +------+-------------+--------------------------+-------+------------------------------+------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-------------+--------------------------+-------+------------------------------+------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 145594 | 140069.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | tc | ref | COMPLETED_TXN_COMPONENTS_IDX | COMPLETED_TXN_COMPONENTS_IDX | 1159 | c.CTC_DATABASE,c.CTC_TABLE,c.CTC_PARTITION | 1 | 1.01 | 100.00 | 0.68 | Using index condition; Using where | | 1 | PRIMARY | | ref | key0 | key0 | 390 | c.CTC_DATABASE,c.CTC_TABLE | 10 | 248.84 | 100.00 | 0.40 | Using where | | 3 | DERIVED | COMPLETED_TXN_COMPONENTS | index | NULL | COMPLETED_TXN_COMPONENTS_IDX | 1159 | NULL | 145594 | 141021.00 | 100.00 | 71.18 | Using where | | 2 | DERIVED | COMPLETED_TXN_COMPONENTS | index | NULL | COMPLETED_TXN_COMPONENTS_IDX | 1159 | NULL | 145594 | 141021.00 | 100.00 | 100.00 | | +------+-------------+--------------------------+-------+------------------------------+------------------------------+---------+--------------------------------------------+--------+-----------+----------+------------+------------------------------------+ 5 rows in set (22.816 sec) MariaDB [test]> MariaDB [test]> set optimizer_switch='split_materialized=on'; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> analyze select * FROM COMPLETED_TXN_COMPONENTS tc INNER JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) highestWriteId FROM COMPLETED_TXN_COMPONENTS GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c ON tc.CTC_DATABASE = c.CTC_DATABASE AND tc.CTC_TABLE = c.CTC_TABLE AND tc.CTC_PARTITION <=> c.CTC_PARTITION LEFT JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) updateWriteId FROM COMPLETED_TXN_COMPONENTS WHERE CTC_UPDATE_DELETE = 'Y' GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c2 ON tc.CTC_DATABASE = c2.CTC_DATABASE AND tc.CTC_TABLE = c2.CTC_TABLE AND tc.CTC_PARTITION <=> c2.CTC_PARTITION WHERE tc.CTC_WRITEID < c.highestWriteId AND NOT tc.CTC_WRITEID <=> c2.updateWriteId; ^[[A +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 145594 | 140069.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | tc | ref | COMPLETED_TXN_COMPONENTS_IDX | COMPLETED_TXN_COMPONENTS_IDX | 1159 | c.CTC_DATABASE,c.CTC_TABLE,c.CTC_PARTITION | 1 | 1.01 | 100.00 | 0.68 | Using index condition; Using where | | 1 | PRIMARY | | ref | key0 | key0 | 390 | c.CTC_DATABASE,c.CTC_TABLE | 2 | 1.00 | 100.00 | 100.00 | Using where | | 3 | LATERAL DERIVED | COMPLETED_TXN_COMPONENTS | ref | COMPLETED_TXN_COMPONENTS_IDX | COMPLETED_TXN_COMPONENTS_IDX | 1159 | test.tc.CTC_DATABASE,test.tc.CTC_TABLE,test.tc.CTC_PARTITION | 1 | 2.00 | 100.00 | 50.00 | Using index condition; Using where | | 2 | DERIVED | COMPLETED_TXN_COMPONENTS | ALL | COMPLETED_TXN_COMPONENTS_IDX | NULL | NULL | NULL | 145594 | 141021.00 | 100.00 | 100.00 | Using temporary; Using filesort | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------------------+--------+-----------+----------+------------+------------------------------------+ 5 rows in set (16.990 sec) MariaDB [test]> MariaDB [test]> analyze select * FROM COMPLETED_TXN_COMPONENTS tc INNER JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) highestWriteId FROM COMPLETED_TXN_COMPONENTS GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c ON tc.CTC_DATABASE = c.CTC_DATABASE AND tc.CTC_TABLE = c.CTC_TABLE AND tc.CTC_PARTITION <=> c.CTC_PARTITION LEFT JOIN ( SELECT CTC_DATABASE, CTC_TABLE, CTC_PARTITION, max(CTC_WRITEID) updateWriteId FROM COMPLETED_TXN_COMPONENTS WHERE CTC_UPDATE_DELETE = 'Y' GROUP BY CTC_DATABASE, CTC_TABLE, CTC_PARTITION ) c2 ON tc.CTC_DATABASE = c2.CTC_DATABASE AND tc.CTC_TABLE = c2.CTC_TABLE AND tc.CTC_PARTITION <=> c2.CTC_PARTITION WHERE tc.CTC_WRITEID < c.highestWriteId AND NOT tc.CTC_WRITEID <=> c2.updateWriteId; +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | r_rows | filtered | r_filtered | Extra | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------------------+--------+-----------+----------+------------+------------------------------------+ | 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 145594 | 140069.00 | 100.00 | 100.00 | Using where | | 1 | PRIMARY | tc | ref | COMPLETED_TXN_COMPONENTS_IDX | COMPLETED_TXN_COMPONENTS_IDX | 1159 | c.CTC_DATABASE,c.CTC_TABLE,c.CTC_PARTITION | 1 | 1.01 | 100.00 | 0.68 | Using index condition; Using where | | 1 | PRIMARY | | ref | key0 | key0 | 390 | c.CTC_DATABASE,c.CTC_TABLE | 2 | 1.00 | 100.00 | 100.00 | Using where | | 3 | LATERAL DERIVED | COMPLETED_TXN_COMPONENTS | ref | COMPLETED_TXN_COMPONENTS_IDX | COMPLETED_TXN_COMPONENTS_IDX | 1159 | test.tc.CTC_DATABASE,test.tc.CTC_TABLE,test.tc.CTC_PARTITION | 1 | 2.00 | 100.00 | 50.00 | Using index condition; Using where | | 2 | DERIVED | COMPLETED_TXN_COMPONENTS | ALL | COMPLETED_TXN_COMPONENTS_IDX | NULL | NULL | NULL | 145594 | 141021.00 | 100.00 | 100.00 | Using temporary; Using filesort | +------+-----------------+--------------------------+------+------------------------------+------------------------------+---------+--------------------------------------------------------------+--------+-----------+----------+------------+------------------------------------+ 5 rows in set (16.992 sec)