create table t1 (i int);

insert into t1 select seq from seq_1_to_1000;

select * from ( select * from t1)dt limit 1;  # select `test`.`t1`.`i` AS `i` from `test`.`t1` limit 1

select * from ( select * from t1 group by i)dt limit 1; #/* select#1 */ select `dt`.`i` AS `i` from (/* select#2 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` group by `test`.`t1`.`i`) `dt` limit 1

MariaDB [test]> explain extended select * from (select * from t1 group by i)dt;

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

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

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

|    1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |                                 |

|    2 | DERIVED     | t1         | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using temporary; Using filesort |

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

2 rows in set, 1 warning (0.001 sec)

MariaDB [test]> explain extended select * from (select * from t1)dt;

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

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

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

|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |

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

1 row in set, 1 warning (0.001 sec)

MariaDB [test]> select * from (select * from t1)dt limit 1;

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.001 sec)

MariaDB [test]> show status like '%Handler_read_rnd%';

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

| Variable_name            | Value |

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

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 1     |

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

3 rows in set (0.002 sec)

MariaDB [test]>  select * from ( select * from t1 group by i)dt limit 1;

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.067 sec)

MariaDB [test]> show status like '%Handler_read_rnd%';

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

| Variable_name            | Value |

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

| Handler_read_rnd         | 1000  |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 2004  |

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

create table t1 (i int);

insert into t1 select seq from seq_1_to_1000;

(select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;

explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;

MariaDB [test]> explain extended (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;

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

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

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

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

|    2 | UNION       | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 |       |

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

2 rows in set, 1 warning (0.003 sec)

MariaDB [test]> flush status;

Query OK, 0 rows affected (0.001 sec)

MariaDB [test]> (select * from t1 limit 100) union all (select * from t1 limit 100) limit 1;

+------+

| i    |

+------+

|    1 |

+------+

1 row in set (0.008 sec)

MariaDB [test]> show status like '%Handler_read_rnd%';

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

| Variable_name            | Value |

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

| Handler_read_rnd         | 0     |

| Handler_read_rnd_deleted | 0     |

| Handler_read_rnd_next    | 200   |

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

3 rows in set (0.006 sec)