CREATE TABLE t1 (a INT, b INT, KEY (a), KEY (a,b));

CREATE TABLE t2 (c INT, KEY (c));

SELECT * FROM t1 t1a JOIN t1 t1b;

INSERT INTO t2 VALUES (1),(2);

INSERT INTO t1 VALUES (1,2),(3,4),(5,6),(7,8),(9,10),(11,12);

SELECT *

FROM

  t1 JOIN

  (SELECT t1.a, t1.b FROM t1, t2 WHERE t1.b = t2.c GROUP BY t1.a, t1.b) as dt

WHERE

  t1.a = dt.a;

show status like "last_query_cost";

DROP TABLE t1, t2;

Variable_name   Value

Last_query_cost 0.000000