Universal Storage in 7.3

universal-storage-7.3.sql

create database db1;
use db1;

create table t(a int not null, shard(a), sort key());

insert t values(1);

delimiter //
/* Fill table t with n or more rows, doubling the size until
 the goal is reached. */
create procedure inflate(n bigint) as
declare
c bigint;
begin
select count(*) from t into c;
while (c < n) loop
  insert into t
  select a + (select max(a) from t)
  from t;
  select count(*) from t into c;
end loop;
end //
delimiter ;

call inflate(1000*1000*1000);

select format(count(*),0) from t;

create table t2(a int not null, unique key(a) using hash, shard(a), sort key());

insert t2 values(1);
insert t2 values(1); /* dup key error */
delete from t2;

/* add rows */
select now(6) into @ts1;
insert into t2 select * from t where t.a <= 100*1000*1000 ; /*takes 52 seconds */
select now(6) into @ts2;
select timestampdiff(SECOND, @ts1, @ts2) as total_time_s;

-- delete from t2;

select format(count(*),0) from t2;

/* 16 s */
select min(a), max(a), avg(a), approx_percentile(a, 0.05), approx_percentile(a, 0.5), approx_percentile(a, 0.95)
from t;

/* 1.8 s */
select min(a), max(a), avg(a), approx_percentile(a, 0.05), approx_percentile(a, 0.5), approx_percentile(a, 0.95)
from t2;