--disable_warnings
drop table if exists t1;
--ensable_warnings
create table t1(a tinyint,b tinyint,c tinyint,d tinyint,e tinyint,f tinyint,
g tinyint,h tinyint,i tinyint,j tinyint,k tinyint,l tinyint,
m tinyint,n tinyint,o tinyint,p tinyint,
key(a),key(b),key(c),key(d),key(e),key(f),key(g),key(h),
key(i),key(j),key(k),key(l),key(m),key(n),key(o),key(p))
engine=myisam;
--disable_query_log
let $count=1000;
while ($count > 0) {
insert into t1 values (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16);
insert into t1 values (2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,1);
insert into t1 values (3,4,5,6,7,8,9,10,11,12,13,14,15,16,1,2);
insert into t1 values (4,5,6,7,8,9,10,11,12,13,14,15,16,1,2,3);
insert into t1 values (5,6,7,8,9,10,11,12,13,14,15,16,1,2,3,4);
insert into t1 values (6,7,8,9,10,11,12,13,14,15,16,1,2,3,4,5);
insert into t1 values (7,8,9,10,11,12,13,14,15,16,1,2,3,4,5,6);
insert into t1 values (8,9,10,11,12,13,14,15,16,1,2,3,4,5,6,7);
insert into t1 values (9,10,11,12,13,14,15,16,1,2,3,4,5,6,7,8);
insert into t1 values (10,11,12,13,14,15,16,1,2,3,4,5,6,7,8,9);
insert into t1 values (11,12,13,14,15,16,1,2,3,4,5,6,7,8,9,10);
insert into t1 values (12,13,14,15,16,1,2,3,4,5,6,7,8,9,10,11);
insert into t1 values (13,14,15,16,1,2,3,4,5,6,7,8,9,10,11,12);
insert into t1 values (14,15,16,1,2,3,4,5,6,7,8,9,10,11,12,13);
insert into t1 values (15,16,1,2,3,4,5,6,7,8,9,10,11,12,13,14);
insert into t1 values (16,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
dec $count;
}
--ensable_query_log
select count(*) from t1;
analyze table t1;
explain
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=1 and b=2 and c=3 and d=4 and e=5 and f=6 and g=7
and h=8 and i=9 and j=10 and k=11 and l=12 and m=13 and n=14 and o=15 and p=16 limit 1;
explain
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=2 and b=3 and c=4 and d=5 and e=6 and f=7 and g=8 and
h=9 and i=10 and j=11 and k=12 and l=13 and m=14 and n=15 and o=16 and p=1 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=3 and b=4 and c=5 and d=6 and e=7 and f=8 and g=9 and
h=10 and i=11 and j=12 and k=13 and l=14 and m=15 and n=16 and o=1 and p=2 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=4 and b=5 and c=6 and d=7 and e=8 and f=9 and g=10 and
h=11 and i=12 and j=13 and k=14 and l=15 and m=16 and n=1 and o=2 and p=3 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=5 and b=6 and c=7 and d=8 and e=9 and f=10 and g=11 and
h=12 and i=13 and j=14 and k=15 and l=16 and m=1 and n=2 and o=3 and p=4 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=6 and b=7 and c=8 and d=9 and e=10 and f=11 and g=12 and
h=13 and i=14 and j=15 and k=16 and l=1 and m=2 and n=3 and o=4 and p=5 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=7 and b=8 and c=9 and d=10 and e=11 and f=12 and g=13 and h=14
and i=15 and j=16 and k=1 and l=2 and m=3 and n=4 and o=5 and p=6 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1 where a=8 and b=9
and c=10 and d=11 and e=12 and f=13 and g=14 and h=15 and i=16 and
j=1 and k=2 and l=3 and m=4 and n=5 and o=6 and p=7 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1 where a=9 and
b=10 and c=11 and d=12 and e=13 and f=14 and g=15 and h=16 and i=1
and j=2 and k=3 and l=4 and m=5 and n=6 and o=7 and p=8 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=10 and b=11 and c=12 and d=13 and e=14 and f=15 and g=16
and h=1 and i=2 and j=3 and k=4 and l=5 and m=6 and n=7 and o=8 and p=9 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1 where a=11 and b=12
and c=13 and d=14 and e=15 and f=16 and g=1 and h=2 and i=3 and
j=4 and k=5 and l=6 and m=7 and n=8 and o=9 and p=10 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=12 and b=13 and c=14 and d=15 and e=16 and f=1 and g=2 and
h=3 and i=4 and j=5 and k=6 and l=7 and m=8 and n=9 and o=10 and p=11 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=13 and b=14 and c=15 and d=16 and e=1 and f=2 and g=3 and
h=4 and i=5 and j=6 and k=7 and l=8 and m=9 and n=10 and o=11 and p=12 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=14 and b=15 and c=16 and d=1 and e=2 and f=3 and g=4 and h=5 and
i=6 and j=7 and k=8 and l=9 and m=10 and n=11 and o=12 and p=13 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=15 and b=16 and c=1 and d=2 and e=3 and f=4 and g=5 and h=6 and
i=7 and j=8 and k=9 and l=10 and m=11 and n=12 and o=13 and p=14 limit 1;
explain select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=16 and b=1 and c=2 and d=3 and e=4 and f=5 and g=6 and h=7 and
i=8 and j=9 and k=10 and l=11 and m=12 and n=13 and o=14 and p=15 limit 1;
let $val1=query_get_value(show global status like "Bytes_allocated", Value, 1);
--disable_result_log
let $count = 100;
while ($count > 0) {
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=1 and b=2 and c=3 and d=4 and e=5 and f=6 and g=7
and h=8 and i=9 and j=10 and k=11 and l=12 and m=13 and n=14 and o=15 and p=16 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=2 and b=3 and c=4 and d=5 and e=6 and f=7 and g=8 and
h=9 and i=10 and j=11 and k=12 and l=13 and m=14 and n=15 and o=16 and p=1 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=3 and b=4 and c=5 and d=6 and e=7 and f=8 and g=9 and
h=10 and i=11 and j=12 and k=13 and l=14 and m=15 and n=16 and o=1 and p=2 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=4 and b=5 and c=6 and d=7 and e=8 and f=9 and g=10 and
h=11 and i=12 and j=13 and k=14 and l=15 and m=16 and n=1 and o=2 and p=3 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=5 and b=6 and c=7 and d=8 and e=9 and f=10 and g=11 and
h=12 and i=13 and j=14 and k=15 and l=16 and m=1 and n=2 and o=3 and p=4 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=6 and b=7 and c=8 and d=9 and e=10 and f=11 and g=12 and
h=13 and i=14 and j=15 and k=16 and l=1 and m=2 and n=3 and o=4 and p=5 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=7 and b=8 and c=9 and d=10 and e=11 and f=12 and g=13 and h=14
and i=15 and j=16 and k=1 and l=2 and m=3 and n=4 and o=5 and p=6 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1 where a=8 and b=9
and c=10 and d=11 and e=12 and f=13 and g=14 and h=15 and i=16 and
j=1 and k=2 and l=3 and m=4 and n=5 and o=6 and p=7 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1 where a=9 and
b=10 and c=11 and d=12 and e=13 and f=14 and g=15 and h=16 and i=1
and j=2 and k=3 and l=4 and m=5 and n=6 and o=7 and p=8 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=10 and b=11 and c=12 and d=13 and e=14 and f=15 and g=16
and h=1 and i=2 and j=3 and k=4 and l=5 and m=6 and n=7 and o=8 and p=9 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1 where a=11 and b=12
and c=13 and d=14 and e=15 and f=16 and g=1 and h=2 and i=3 and
j=4 and k=5 and l=6 and m=7 and n=8 and o=9 and p=10 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=12 and b=13 and c=14 and d=15 and e=16 and f=1 and g=2 and
h=3 and i=4 and j=5 and k=6 and l=7 and m=8 and n=9 and o=10 and p=11 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=13 and b=14 and c=15 and d=16 and e=1 and f=2 and g=3 and
h=4 and i=5 and j=6 and k=7 and l=8 and m=9 and n=10 and o=11 and p=12 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=14 and b=15 and c=16 and d=1 and e=2 and f=3 and g=4 and h=5 and
i=6 and j=7 and k=8 and l=9 and m=10 and n=11 and o=12 and p=13 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=15 and b=16 and c=1 and d=2 and e=3 and f=4 and g=5 and h=6 and
i=7 and j=8 and k=9 and l=10 and m=11 and n=12 and o=13 and p=14 limit 1;
select a,b,c,d,e,f,g,h,i,j,k,l,o,p from t1
where a=16 and b=1 and c=2 and d=3 and e=4 and f=5 and g=6 and h=7 and
i=8 and j=9 and k=10 and l=11 and m=12 and n=13 and o=14 and p=15 limit 1;
dec $count;
}
--enable_result_log
let $val2=query_get_value(show global status like "Bytes_allocated", Value, 1);
let $diff=`select $val2 - $val1`;
echo Bytes allocated at start is $val1 and at end is $val2 with difference $diff;
eval select 'Bytes allocated difference less than 10,000', $diff < 10000 as diff;