CREATE TABLE t1 (

n1 int(10) NOT NULL,

n2 int(10) NOT NULL,

c1 char(1) NOT NULL,

KEY c1 (c1),

KEY n1_c1_n2 (n1,c1,n2)

) ENGINE=InnoDB;

INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a');

ANALYZE TABLE t1;

EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t                                                  

WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1;                                                                          

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

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

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

|    1 | PRIMARY         | t1         | index | c1,n1_c1_n2   | n1_c1_n2 | 9       | NULL              |    2 | Using where; Using index |

|    1 | PRIMARY         | <derived2> | ref   | key0          | key0     | 8       | j5.t1.n1,j5.t1.n2 |    2 |                          |

|    2 | LATERAL DERIVED | t1         | ref   | c1,n1_c1_n2   | n1_c1_n2 | 4       | j5.t1.n1          |    1 | Using where; Using index |

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

{

  "query_block": {

    "select_id": 1,

    "table": {

      "table_name": "t1",

      "access_type": "index",

      "possible_keys": ["c1", "n1_c1_n2"],

      "key": "n1_c1_n2",

      "key_length": "9",

      "used_key_parts": ["n1", "c1", "n2"],

      "rows": 2,

      "filtered": 100,

      "attached_condition": "t1.c1 <=> 'a' and t1.c1 = 'a'",

      "using_index": true

    },

    "table": {

      "table_name": "<derived2>",

      "access_type": "ref",

      "possible_keys": ["key0"],

      "key": "key0",

      "key_length": "8",

      "used_key_parts": ["n1", "n2"],

      "ref": ["j5.t1.n1", "j5.t1.n2"],

      "rows": 2,

      "filtered": 100,

      "materialized": {

        "query_block": {

          "select_id": 2,

          "table": {

            "table_name": "t1",

            "access_type": "ref",

            "possible_keys": ["c1", "n1_c1_n2"],

            "key": "n1_c1_n2",

            "key_length": "4",

            "used_key_parts": ["n1"],

            "ref": ["j5.t1.n1"],

            "rows": 1,

            "filtered": 100,

            "attached_condition": "t1.c1 = 'a'",

            "using_index": true

          }

        }

      }

    }

  }

}