--source include/have_innodb.inc

create table t1(a int) engine=innodb;

insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

create table t2(

  id int primary key,

  key1 int,

  col1 int,

  key(key1)

) engine=innodb;

insert into t2

  select

    A.a + B.a*10 + C.a*100 + D.a* 1000,

    A.a + 10*B.a,

    123456

from t1 A, t1 B, t1 C, t1 D;

alter table t2 add key2 int;

update t2 set key2=key1;

alter table t2 add key(key2);

analyze table t2;

flush tables;

explain select

   (SELECT

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

    FROM t2

    WHERE t2.key1 = t1.a

    ORDER BY t2.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      |   10 |                             |

|    2 | DEPENDENT SUBQUERY | t2    | ref  | key1          | key1 | 5       | test.t1.a |   49 | Using where; Using filesort |

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

explain select

     (SELECT

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

     FROM t2

      WHERE t2.key1 = t1.a and t2.key1 is NOT NULL

     ORDER BY t2.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 |   10 |             |

|    2 | DEPENDENT SUBQUERY | t2    | index | key1          | key2 | 5       | NULL |    2 | Using where |

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