1# 2014-06-17 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#************************************************************************* 11# 12# This file implements regression tests for SQLite library. The 13# focus of this script is testing automatic index creation logic. 14# 15# This file contains a single real-world test case that was giving 16# suboptimal performance because of over-use of automatic indexes. 17# 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22 23do_execsql_test autoindex2-100 { 24 CREATE TABLE t1( 25 t1_id largeint, 26 did char(9), 27 ptime largeint, 28 exbyte char(4), 29 pe_id int, 30 field_id int, 31 mass float, 32 param10 float, 33 param11 float, 34 exmass float, 35 deviation float, 36 trange float, 37 vstatus int, 38 commit_status int, 39 formula char(329), 40 tier int DEFAULT 2, 41 ssid int DEFAULT 0, 42 last_operation largeint DEFAULT 0, 43 admin_uuid int DEFAULT 0, 44 previous_value float, 45 job_id largeint, 46 last_t1 largeint DEFAULT 0, 47 data_t1 int, 48 previous_date largeint DEFAULT 0, 49 flg8 int DEFAULT 1, 50 failed_fields char(100) 51 ); 52 CREATE INDEX t1x0 on t1 (t1_id); 53 CREATE INDEX t1x1 on t1 (ptime, vstatus); 54 CREATE INDEX t1x2 on t1 (did, ssid, ptime, vstatus, exbyte, t1_id); 55 CREATE INDEX t1x3 on t1 (job_id); 56 57 CREATE TABLE t2( 58 did char(9), 59 client_did char(30), 60 description char(49), 61 uid int, 62 tzid int, 63 privilege int, 64 param2 int, 65 type char(30), 66 subtype char(32), 67 dparam1 char(7) DEFAULT '', 68 param5 char(3) DEFAULT '', 69 notional float DEFAULT 0.000000, 70 create_time largeint, 71 sample_time largeint DEFAULT 0, 72 param6 largeint, 73 frequency int, 74 expiration largeint, 75 uw_status int, 76 next_sample largeint, 77 last_sample largeint, 78 reserve1 char(29) DEFAULT '', 79 reserve2 char(29) DEFAULT '', 80 reserve3 char(29) DEFAULT '', 81 bxcdr char(19) DEFAULT 'XY', 82 ssid int DEFAULT 1, 83 last_t1_id largeint, 84 reserve4 char(29) DEFAULT '', 85 reserve5 char(29) DEFAULT '', 86 param12 int DEFAULT 0, 87 long_did char(100) DEFAULT '', 88 gr_code int DEFAULT 0, 89 drx char(100) DEFAULT '', 90 parent_id char(9) DEFAULT '', 91 param13 int DEFAULT 0, 92 position float DEFAULT 1.000000, 93 client_did3 char(100) DEFAULT '', 94 client_did4 char(100) DEFAULT '', 95 dlib_id char(9) DEFAULT '' 96 ); 97 CREATE INDEX t2x0 on t2 (did); 98 CREATE INDEX t2x1 on t2 (client_did); 99 CREATE INDEX t2x2 on t2 (long_did); 100 CREATE INDEX t2x3 on t2 (uid); 101 CREATE INDEX t2x4 on t2 (param2); 102 CREATE INDEX t2x5 on t2 (type); 103 CREATE INDEX t2x6 on t2 (subtype); 104 CREATE INDEX t2x7 on t2 (last_sample); 105 CREATE INDEX t2x8 on t2 (param6); 106 CREATE INDEX t2x9 on t2 (frequency); 107 CREATE INDEX t2x10 on t2 (privilege); 108 CREATE INDEX t2x11 on t2 (sample_time); 109 CREATE INDEX t2x12 on t2 (notional); 110 CREATE INDEX t2x13 on t2 (tzid); 111 CREATE INDEX t2x14 on t2 (gr_code); 112 CREATE INDEX t2x15 on t2 (parent_id); 113 114 CREATE TABLE t3( 115 uid int, 116 param3 int, 117 uuid int, 118 acc_id int, 119 cust_num int, 120 numerix_id int, 121 pfy char(29), 122 param4 char(29), 123 param15 int DEFAULT 0, 124 flg7 int DEFAULT 0, 125 param21 int DEFAULT 0, 126 bxcdr char(2) DEFAULT 'PC', 127 c31 int DEFAULT 0, 128 c33 int DEFAULT 0, 129 c35 int DEFAULT 0, 130 c37 int, 131 mgr_uuid int, 132 back_up_uuid int, 133 priv_mars int DEFAULT 0, 134 is_qc int DEFAULT 0, 135 c41 int DEFAULT 0, 136 deleted int DEFAULT 0, 137 c47 int DEFAULT 1 138 ); 139 CREATE INDEX t3x0 on t3 (uid); 140 CREATE INDEX t3x1 on t3 (param3); 141 CREATE INDEX t3x2 on t3 (uuid); 142 CREATE INDEX t3x3 on t3 (acc_id); 143 CREATE INDEX t3x4 on t3 (param4); 144 CREATE INDEX t3x5 on t3 (pfy); 145 CREATE INDEX t3x6 on t3 (is_qc); 146 SELECT count(*) FROM sqlite_master; 147} {30} 148do_execsql_test autoindex2-110 { 149 ANALYZE sqlite_master; 150 INSERT INTO sqlite_stat1 VALUES('t1','t1x3','10747267 260'); 151 INSERT INTO sqlite_stat1 VALUES('t1','t1x2','10747267 121 113 2 2 2 1'); 152 INSERT INTO sqlite_stat1 VALUES('t1','t1x1','10747267 50 40'); 153 INSERT INTO sqlite_stat1 VALUES('t1','t1x0','10747267 1'); 154 INSERT INTO sqlite_stat1 VALUES('t2','t2x15','39667 253'); 155 INSERT INTO sqlite_stat1 VALUES('t2','t2x14','39667 19834'); 156 INSERT INTO sqlite_stat1 VALUES('t2','t2x13','39667 13223'); 157 INSERT INTO sqlite_stat1 VALUES('t2','t2x12','39667 7'); 158 INSERT INTO sqlite_stat1 VALUES('t2','t2x11','39667 17'); 159 INSERT INTO sqlite_stat1 VALUES('t2','t2x10','39667 19834'); 160 INSERT INTO sqlite_stat1 VALUES('t2','t2x9','39667 7934'); 161 INSERT INTO sqlite_stat1 VALUES('t2','t2x8','39667 11'); 162 INSERT INTO sqlite_stat1 VALUES('t2','t2x7','39667 5'); 163 INSERT INTO sqlite_stat1 VALUES('t2','t2x6','39667 242'); 164 INSERT INTO sqlite_stat1 VALUES('t2','t2x5','39667 1984'); 165 INSERT INTO sqlite_stat1 VALUES('t2','t2x4','39667 4408'); 166 INSERT INTO sqlite_stat1 VALUES('t2','t2x3','39667 81'); 167 INSERT INTO sqlite_stat1 VALUES('t2','t2x2','39667 551'); 168 INSERT INTO sqlite_stat1 VALUES('t2','t2x1','39667 2'); 169 INSERT INTO sqlite_stat1 VALUES('t2','t2x0','39667 1'); 170 INSERT INTO sqlite_stat1 VALUES('t3','t3x6','569 285'); 171 INSERT INTO sqlite_stat1 VALUES('t3','t3x5','569 2'); 172 INSERT INTO sqlite_stat1 VALUES('t3','t3x4','569 2'); 173 INSERT INTO sqlite_stat1 VALUES('t3','t3x3','569 5'); 174 INSERT INTO sqlite_stat1 VALUES('t3','t3x2','569 3'); 175 INSERT INTO sqlite_stat1 VALUES('t3','t3x1','569 6'); 176 INSERT INTO sqlite_stat1 VALUES('t3','t3x0','569 1'); 177 ANALYZE sqlite_master; 178} {} 179do_execsql_test autoindex2-120 { 180 EXPLAIN QUERY PLAN 181 SELECT 182 t1_id, 183 t1.did, 184 param2, 185 param3, 186 t1.ptime, 187 t1.trange, 188 t1.exmass, 189 t1.mass, 190 t1.vstatus, 191 type, 192 subtype, 193 t1.deviation, 194 t1.formula, 195 dparam1, 196 reserve1, 197 reserve2, 198 param4, 199 t1.last_operation, 200 t1.admin_uuid, 201 t1.previous_value, 202 t1.job_id, 203 client_did, 204 t1.last_t1, 205 t1.data_t1, 206 t1.previous_date, 207 param5, 208 param6, 209 mgr_uuid 210 FROM 211 t1, 212 t2, 213 t3 214 WHERE 215 t1.ptime > 1393520400 216 AND param3<>9001 217 AND t3.flg7 = 1 218 AND t1.did = t2.did 219 AND t2.uid = t3.uid 220 ORDER BY t1.ptime desc LIMIT 500; 221} {~/AUTO/} 222# 223# ^^^--- Before being fixed, the above was using an automatic covering 224# on t3 and reordering the tables so that t3 was in the outer loop and 225# implementing the ORDER BY clause using a B-Tree. 226# 227# This test is sanitized data received from a user. The original unsanitized 228# data and STAT4 data is found in the th3private test repository. See one of 229# the th3private check-ins on 2016-02-25. The test is much more accurate when 230# STAT4 data is used. 231 232finish_test 233