try it yourself
drop table if exists value;
create table value (
id int primary key auto_increment,
val int not null,
index (val)
);
-- insert 10,192,056 rows
-- no index (42sec)
-- with index (53sec)
insert into value(val) values (1),(2),(3),(4),(5),(6),(7);
insert into value(val) select (2*b.val + a.val) mod 3 as val from value as a, value b;
insert into value(val) select (2*b.val + a.val) mod 3 as val from value as a, value b;
insert into value(val) select (2*b.val + a.val) mod 3 as val from value as a, value b;
-- first time slow, second time faster
select count(1) from value;
select count(1) from value;
-- first time slow, second time faster
select val, count(1) from value group by val;
select val, count(1) from value group by val;
-- slow after insertion or deletion
insert into value(val) values (-1);
select count(1) from value;
delete from value where valu = -1;
select count(1) from value;
-- or first time after update 1 row
update value set val = (val+1) mod 3 where id = 100;
select val, count(1) from value group by val;
select count(1) from value;