random access with buckets, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

drop table if exists mytable\\
create table mytable (id int)\\

insert into mytable
select ((((a.col * 7 + b.col) * 7 + c.col) * 7 + d.col) * 7 + e.col) * 7 + f.col
from (select 0 as col union all select 1 as col union all select 2 union all select 3 union all select 4 union all select 5 union all select 6) a
cross join (select 0 as col union all select 1 as col union all select 2 union all select 3 union all select 4 union all select 5 union all select 6) b
cross join (select 0 as col union all select 1 as col union all select 2 union all select 3 union all select 4 union all select 5 union all select 6) c
cross join (select 0 as col union all select 1 as col union all select 2 union all select 3 union all select 4 union all select 5 union all select 6) d
cross join (select 0 as col union all select 1 as col union all select 2 union all select 3 union all select 4 union all select 5 union all select 6) e
cross join (select 0 as col union all select 1 as col union all select 2 union all select 3 union all select 4 union all select 5 union all select 6) f
order by 1\\

alter table mytable add column bucket int GENERATED ALWAYS AS (id % 997) stored\\
create index idx on mytable(bucket)\\

select *
from mytable
where bucket = floor(rand() * 998)
order by rand()
limit 10\\

drop table mytable\\


absolute service time: 3,09 sec