What is the practical difference between the two types VIRTUAL and STORED?

Question

MySQL

Hello!

CREATE TABLE `tmp_1` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `energy` smallint(5) unsigned NOT NULL DEFAULT 0,
 `energy_max` tinyint(3) unsigned NOT NULL DEFAULT 0,
 `energy_up` tinyint(3) unsigned GENERATED ALWAYS AS (`energy` < `energy_max`) STORED,
 PRIMARY KEY (`id`),
 KEY `energy_up` (`energy_up`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

CREATE TABLE `tmp_2` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `energy` smallint(5) unsigned NOT NULL DEFAULT 0,
 `energy_max` tinyint(3) unsigned NOT NULL DEFAULT 0,
 `energy_up` tinyint(3) unsigned GENERATED ALWAYS AS (`energy` < `energy_max`) VIRTUAL,
 PRIMARY KEY (`id`),
 KEY `energy_up` (`energy_up`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT

There are two identical tables, but in one case the energy_up field is of the STORED type, in the other it is VIRTUAL.
Below is my reasoning, I would like to know how right I am
As far as I understand, in the case of STORED:
- the result of the operation will be calculated only when adding / changing a record;
- the result will be stored on the hard drive;
- the index on this field will be stored on the hard disk.
In the case of VIRTUAL:
- the result of the operation will be calculated every time it is read;
- the index on this field will be stored in RAM.
Therefore, if I do not have an index on this field, then when querying
SELECT id FROM tmp_2 WHERE energy_up = 1
- first, a full table scan will be performed and the field value for each record will be calculated (a virtual table may be created);
- then energy_up = 1 will be searched in the virtual table.
Thus, if there is no index, it is more profitable to use STORED, because the table file will simply be scanned, if there is an index, it is more profitable to use VIRTUAL, because it is stored in memory, which is faster than reading the file.
Question: what is the difference between these two field types and in what cases which type should be used?
Thanks for answers!

Answer 1

In the case of VIRTUAL:
- the result of the operation will be calculated every time it is read;
- the index on this field will be stored in RAM.

The index will also be stored as a normal index.
Through a virtual generated column, it was previously possible to emulate a functional index. Now this is no longer necessary.
The only difference is that stored is stored as part of the table, but is only evaluated when it is written. And virtual is not stored and is calculated when reading.