#!/bin/bash

# Assumes that the server is in /tmp/mysql4.sock

mysql -uroot --socket=/tmp/mysql4.sock -e"create database j20"

cat > 1-fill.sql << EOF
# A table to be materialized.
#  Range access will be used to get the desired # of records
create or replace table t1 
( 
  a int,
  b int,
  filler1 varchar(200),
  index(a)
);

insert into t1 select seq, seq,repeat(seq,8) from seq_1_to_20000000;

# Will make probes with t2.
#  Put enough rows so that Materialization is justified
create or replace table t2 
(
  b int
);
insert into t2 select  seq from seq_1_to_10000;
analyze table t1,t2;
EOF

mysql -uroot --socket=/tmp/mysql4.sock j20 < 1-fill.sql

mysql -uroot --socket=/tmp/mysql4.sock j20 -e "select count(*) from t1"

rm q*.json

for i in 100000 200000 400000 800000 ; do 
  mysql -uroot --socket=/tmp/mysql4.sock --raw \
    -e "set max_heap_table_size=16000;" \
    -e "analyze format=json select * from t2 where b in (select b from t1 where a < $i)" j20 > q_$i.json 
done 

cat > parse-query-analyze.pl << END
while(<>) {
  last if /materialized/;
}
while(<>) { 
  if (/materialization_cost": ([0-9.]+)/) {
    print $1 ."\n";
    last;
  }
}

while(<>) {
  if (/"rows": ([0-9.]+)/) {
    print $1 ."\n";
  }
  if (/"cost": ([0-9.]+)/) {
    print $1 ."\n";
  }
  if (/"r_table_time_ms": ([0-9.]+)/) {
    print $1 ."\n";
  }
  if (/"r_other_time_ms": ([0-9.]+)/) {
    print $1 ."\n";
  }
}
END

echo "materialization_cost rows cost r_table_time_ms r_other_time_ms" > out.csv

for i in 100000 200000 400000 800000 ; do 
  perl 1.pl < q_$i.json >> out.csv
done
########### 

#  Get the cost of just checking the WHERE 
for i in 100000 200000 400000 800000 ; do 
  mysql -uroot --socket=/tmp/mysql4.sock --raw \
    -e "analyze format=json select b from t1 where a < $i" j20 > subq_q_$i.json 
done 

cat > parse-subq-q-analyze.pl << 'END'
#!/usr/bin/perl
$SEP="\t";
while(<>) {
  if (/"rows": ([0-9.]+)/) {
    print $1 . $SEP;
  }
  if (/"cost": ([0-9.]+)/) {
    print $1 . $SEP;
  }
  if (/"r_table_time_ms": ([0-9.]+)/) {
    print $1 . $SEP;
  }
  if (/"r_other_time_ms": ([0-9.]+)/) {
    print $1 . $SEP;
  }
}
print "\n";
END

echo "rows\tcost\tr_table_time_ms\tr_other_time_ms" > out-subq.csv
for i in 100000 200000 400000 800000 ; do 
  perl  parse-subq-q-analyze.pl   < subq_q_$i.json >> out-subq.csv
done

