| OLD | NEW |
|---|
| (Empty) |
| 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 | |
| 19 set testdir [file dirname $argv0] | |
| 20 source $testdir/tester.tcl | |
| 21 | |
| 22 | |
| 23 do_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} | |
| 148 do_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 } {} | |
| 179 do_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 } {0 0 0 {SEARCH TABLE t1 USING INDEX t1x1 (ptime>?)} 0 1 1 {SEARCH TABLE t2 USI
NG INDEX t2x0 (did=?)} 0 2 2 {SEARCH TABLE t3 USING INDEX t3x0 (uid=?)}} | |
| 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 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
