--source include/have_innodb.inc

create table t1(a int,  key(a))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;

analyze table t2;

flush tables;

analyze format=json select

(SELECT

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

FROM t2

WHERE t2.key1 = t1.a

ORDER BY t2.key2 ASC limit 1)

from

t1;

ANALYZE

{

  "query_block": {

    "select_id": 1,

    "r_loops": 1,

    "r_total_time_ms": 852.7,

    "table": {

      "table_name": "t1",

      "access_type": "index",

      "key": "a",

      "key_length": "5",

      "used_key_parts": ["a"],

      "r_loops": 1,

      "rows": 10,

      "r_rows": 10,

      "r_total_time_ms": 2.819,

      "filtered": 100,

      "r_filtered": 100,

      "using_index": true

    },

    "subqueries": [

      {

        "expression_cache": {

          "r_loops": 10,

          "r_hit_ratio": 0,

          "query_block": {

            "select_id": 2,

            "r_loops": 10,

            "r_total_time_ms": 848.11,

            "outer_ref_condition": "t1.a is not null",

            "read_sorted_file": {

              "r_rows": 1,

              "filesort": {

                "sort_key": "t2.key2",

                "r_loops": 10,

                "r_total_time_ms": 844.61,

                "r_limit": 0,

                "r_used_priority_queue": true,

                "r_output_rows": 2,

                "table": {

                  "table_name": "t2",

                  "access_type": "ref",

                  "possible_keys": ["key1"],

                  "key": "key1",

                  "key_length": "5",

                  "used_key_parts": ["key1"],

                  "ref": ["test.t1.a"],

                  "r_loops": 10,

                  "rows": 48,

                  "r_rows": 100,

                  "r_total_time_ms": 831.09,

                  "filtered": 100,

                  "r_filtered": 1,

                  "attached_condition": ""

                }

              }

            }

          }

        }

      }

    ]

  }

}