1# Verify that index and range scans are not slow 2# on tables during insert select statements 3# due to tokudb bulk fetch not being used 4 5source include/have_tokudb.inc; 6source include/big_test.inc; 7set default_storage_engine='tokudb'; 8disable_warnings; 9drop table if exists t1,t2; 10enable_warnings; 11 12let $debug = 0; 13 14CREATE TABLE `t1` ( 15 `num` int(10) unsigned auto_increment NOT NULL, 16 `val` varchar(32) DEFAULT NULL, 17 PRIMARY KEY (`num`) 18); 19 20# Create second table t2 that will serve as the target for the insert select statment 21CREATE TABLE `t2` ( 22 `count` bigint(20) NOT NULL 23); 24 25# put 1M rows into t1 26INSERT INTO t1 values (null,null); 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; 44INSERT INTO t1 SELECT null,val FROM t1; 45INSERT INTO t1 SELECT null,val FROM t1; 46INSERT INTO t1 SELECT null,val FROM t1; 47SELECT count(*) FROM t1; 48 49let $maxq = 20; 50 51set tokudb_bulk_fetch=ON; 52let $s = `select unix_timestamp()`; 53let $i = 0; 54while ($i < $maxq) { 55 INSERT into t2 SELECT count(*) from t1; 56 inc $i; 57} 58let $time_elapsed_on = `select unix_timestamp() - $s`; 59 60set tokudb_bulk_fetch=OFF; 61let $s = `select unix_timestamp()`; 62let $i = 0; 63while ($i < $maxq) { 64 INSERT into t2 SELECT count(*) from t1; 65 inc $i; 66} 67let $time_elapsed_off = `select unix_timestamp() - $s`; 68 69# check that the time with bulk fetch on is greater than with bulk fetch off 70let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off > $time_elapsed_on`; 71echo $verdict; 72if ($debug) { echo index $verdict $time_elapsed_off $time_elapsed_on; } 73if (!$verdict) { echo index $time_elapsed_off $time_elapsed_on; } 74 75let $maxq = 20; 76 77set tokudb_bulk_fetch=ON; 78let $s = `select unix_timestamp()`; 79let $i = 0; 80while ($i < $maxq) { 81 INSERT into t2 SELECT count(*) from t1 where num > 700000; 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 INSERT into t2 SELECT count(*) from t1 where num > 700000; 91 inc $i; 92} 93let $time_elapsed_off = `select unix_timestamp() - $s`; 94 95let $verdict = `select $time_elapsed_on > 0 && $time_elapsed_off > $time_elapsed_on`; 96echo $verdict; 97if ($debug) { echo range $verdict $time_elapsed_off $time_elapsed_on; } 98if (!$verdict) { echo range $time_elapsed_off $time_elapsed_on; } 99 100drop table t1,t2; 101