create table t1 (a int);

insert into t1 select * from test.one_k;

create table tsubq(

  id int primary key,

  key1 int,

  col1 int,

  key(key1)

) engine=innodb;

insert into tsubq 

  select A.a + B.a*1000, A.a, 123456 from test.one_k A, test.one_k B;

alter table tsubq add key2 int;

update tsubq set key2=key1;

alter table tsubq add key(key2);

explain select

     (SELECT

        concat(id, '-', key1, '-', col1)

     FROM tsubq

      WHERE tsubq.key1 = t1.a

     ORDER BY tsubq.key2 ASC LIMIT 1)

from t1;

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

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

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

|    1 | PRIMARY            | t1    | ALL   | NULL          | NULL | NULL    | NULL | 1000 |             |

|    2 | DEPENDENT SUBQUERY | tsubq | index | key1          | key2 | 5       | NULL |    1 | Using where |

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

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

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

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

|  1 | PRIMARY            | t1    | ALL  | NULL          | NULL | NULL    | NULL    |    1 | NULL                        |

|  2 | DEPENDENT SUBQUERY | tsubq | ref  | key1          | key1 | 5       | j5.t1.a |  532 | Using where; Using filesort |

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