1# Verify that index and range scans are not slow 2# on tables during create select statements 3# due to tokudb bulk fetch not being used 4 5source include/have_tokudb.inc; 6source include/have_partition.inc; 7source include/big_test.inc; 8set default_storage_engine='tokudb'; 9disable_warnings; 10drop table if exists t1,t2; 11enable_warnings; 12 13let $debug = 0; 14 15# Create source hash partitioned table from source table t 16CREATE TABLE `t1` ( 17 `num` int(10) unsigned auto_increment NOT NULL, 18 `val` varchar(32) DEFAULT NULL, 19 PRIMARY KEY (`num`) 20) PARTITION BY HASH (num) PARTITIONS 8; 21 22# put 1M rows into t1 23INSERT INTO t1 values (null,null); 24INSERT INTO t1 SELECT null,val FROM t1; 25INSERT INTO t1 SELECT null,val FROM t1; 26INSERT INTO t1 SELECT null,val FROM t1; 27INSERT INTO t1 SELECT null,val FROM t1; 28INSERT INTO t1 SELECT null,val FROM t1; 29INSERT INTO t1 SELECT null,val FROM t1; 30INSERT INTO t1 SELECT null,val FROM t1; 31INSERT INTO t1 SELECT null,val FROM t1; 32INSERT INTO t1 SELECT null,val FROM t1; 33INSERT INTO t1 SELECT null,val FROM t1; 34INSERT INTO t1 SELECT null,val FROM t1; 35INSERT INTO t1 SELECT null,val FROM t1; 36INSERT INTO t1 SELECT null,val FROM t1; 37INSERT INTO t1 SELECT null,val FROM t1; 38INSERT INTO t1 SELECT null,val FROM t1; 39INSERT INTO t1 SELECT null,val FROM t1; 40INSERT INTO t1 SELECT null,val FROM t1; 41INSERT INTO t1 SELECT null,val FROM t1; 42INSERT INTO t1 SELECT null,val FROM t1; 43INSERT INTO t1 SELECT null,val FROM t1; 44SELECT count(*) FROM t1; 45 46let $maxq = 20; 47 48set tokudb_bulk_fetch=ON; 49let $s = `select unix_timestamp()`; 50let $i = 0; 51while ($i < $maxq) { 52 CREATE TABLE t2 AS SELECT count(*) from t1; 53 DROP TABLE t2; 54 inc $i; 55} 56let $time_elapsed_on = `select unix_timestamp() - $s`; 57 58set tokudb_bulk_fetch=OFF; 59let $s = `select unix_timestamp()`; 60let $i = 0; 61while ($i < $maxq) { 62 CREATE TABLE t2 AS SELECT count(*) from t1; 63 DROP TABLE t2; 64 inc $i; 65} 66let $time_elapsed_off = `select unix_timestamp() - $s`; 67 68# check that bulk fetch on is greater than bulk fetch off 69let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off > $time_elapsed_on`; 70echo $verdict; 71if ($debug) { echo index $verdict $time_elapsed_off $time_elapsed_on; } 72if (!$verdict) { echo index $time_elapsed_off $time_elapsed_on; } 73 74let $maxq = 20; 75 76set tokudb_bulk_fetch=ON; 77let $s = `select unix_timestamp()`; 78let $i = 0; 79while ($i < $maxq) { 80 CREATE TABLE t2 AS SELECT count(*) from t1 where num > 700000; 81 DROP TABLE t2; 82 inc $i; 83} 84let $time_elapsed_on = `select unix_timestamp() - $s`; 85 86set tokudb_bulk_fetch=OFF; 87let $s = `select unix_timestamp()`; 88let $i = 0; 89while ($i < $maxq) { 90 CREATE TABLE t2 AS SELECT count(*) from t1 where num > 700000; 91 DROP TABLE t2; 92 inc $i; 93} 94let $time_elapsed_off = `select unix_timestamp() - $s`; 95 96# check that bulk fetch on is greater than bulk fetch off 97let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off > $time_elapsed_on`; 98echo $verdict; 99if ($debug) { echo range $verdict $time_elapsed_off $time_elapsed_on; } 100if (!$verdict) { echo range $time_elapsed_off $time_elapsed_on; } 101 102drop table t1; 103