drop table if exists tdummy;

create table tdummy(id int auto_increment primary key, c1 int, key(c1)) engine=InnoDB;

insert into tdummy(c1) values(1);

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

insert into tdummy(c1) select 2 from tdummy;

alter table tdummy add column c2 char(200) default 'a';

select version();

explain select max(c1) from tdummy;

explain select @max:=max(c1) from tdummy;

select max(c1) from tdummy;

select @max:=max(c1) from tdummy;

...

MariaDB [test]> insert into tdummy(c1) select 2 from tdummy;

Query OK, 524288 rows affected (4.88 sec)

Records: 524288  Duplicates: 0  Warnings: 0

MariaDB [test]> alter table tdummy add column c2 char(200) default 'a';

Query OK, 0 rows affected (45.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> select version();

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

| version()       |

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

| 10.1.18-MariaDB |

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

1 row in set (0.00 sec)

MariaDB [test]> explain select max(c1) from tdummy;

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

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

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

|    1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |

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

1 row in set (0.02 sec)

MariaDB [test]> explain select @max:=max(c1) from tdummy;

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

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

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

|    1 | SIMPLE      | tdummy | index | NULL          | c1   | 5       | NULL | 1032785 | Using index |

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

1 row in set (0.00 sec)

MariaDB [test]> select max(c1) from tdummy;

+---------+

| max(c1) |

+---------+

|       2 |

+---------+

1 row in set (0.00 sec)

MariaDB [test]> select @max:=max(c1) from tdummy;

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

| @max:=max(c1) |

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

|             2 |

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

1 row in set (0.24 sec)