| OLD | NEW |
|---|
| (Empty) |
| 1 # 2010 April 07 | |
| 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 # This file implements regression tests for SQLite library. The | |
| 12 # focus of this script is testing automatic index creation logic. | |
| 13 # | |
| 14 | |
| 15 set testdir [file dirname $argv0] | |
| 16 source $testdir/tester.tcl | |
| 17 | |
| 18 # If the library is not compiled with automatic index support then | |
| 19 # skip all tests in this file. | |
| 20 # | |
| 21 ifcapable {!autoindex} { | |
| 22 finish_test | |
| 23 return | |
| 24 } | |
| 25 | |
| 26 # Setup for logging | |
| 27 db close | |
| 28 sqlite3_shutdown | |
| 29 test_sqlite3_log [list lappend ::log] | |
| 30 set ::log [list] | |
| 31 sqlite3 db test.db | |
| 32 | |
| 33 | |
| 34 # With automatic index turned off, we do a full scan of the T2 table | |
| 35 do_test autoindex1-100 { | |
| 36 db eval { | |
| 37 CREATE TABLE t1(a,b); | |
| 38 INSERT INTO t1 VALUES(1,11); | |
| 39 INSERT INTO t1 VALUES(2,22); | |
| 40 INSERT INTO t1 SELECT a+2, b+22 FROM t1; | |
| 41 INSERT INTO t1 SELECT a+4, b+44 FROM t1; | |
| 42 CREATE TABLE t2(c,d); | |
| 43 INSERT INTO t2 SELECT a, 900+b FROM t1; | |
| 44 } | |
| 45 db eval { | |
| 46 PRAGMA automatic_index=OFF; | |
| 47 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; | |
| 48 } | |
| 49 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 50 do_test autoindex1-101 { | |
| 51 db status step | |
| 52 } {63} | |
| 53 do_test autoindex1-102 { | |
| 54 db status autoindex | |
| 55 } {0} | |
| 56 | |
| 57 # With autoindex turned on, we build an index once and then use that index | |
| 58 # to find T2 values. | |
| 59 do_test autoindex1-110 { | |
| 60 db eval { | |
| 61 PRAGMA automatic_index=ON; | |
| 62 SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b; | |
| 63 } | |
| 64 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 65 do_test autoindex1-111 { | |
| 66 db status step | |
| 67 } {7} | |
| 68 do_test autoindex1-112 { | |
| 69 db status autoindex | |
| 70 } {7} | |
| 71 do_test autoindex1-113 { | |
| 72 set ::log | |
| 73 } {SQLITE_WARNING_AUTOINDEX {automatic index on t2(c)}} | |
| 74 | |
| 75 db close | |
| 76 sqlite3_shutdown | |
| 77 test_sqlite3_log | |
| 78 sqlite3_initialize | |
| 79 sqlite3 db test.db | |
| 80 | |
| 81 # The same test as above, but this time the T2 query is a subquery rather | |
| 82 # than a join. | |
| 83 do_test autoindex1-200 { | |
| 84 db eval { | |
| 85 PRAGMA automatic_index=OFF; | |
| 86 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; | |
| 87 } | |
| 88 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 89 do_test autoindex1-201 { | |
| 90 db status step | |
| 91 } {35} | |
| 92 do_test autoindex1-202 { | |
| 93 db status autoindex | |
| 94 } {0} | |
| 95 do_test autoindex1-210 { | |
| 96 db eval { | |
| 97 PRAGMA automatic_index=ON; | |
| 98 ANALYZE; | |
| 99 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t1'; | |
| 100 -- Table t2 actually contains 8 rows. | |
| 101 UPDATE sqlite_stat1 SET stat='16' WHERE tbl='t2'; | |
| 102 ANALYZE sqlite_master; | |
| 103 SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1; | |
| 104 } | |
| 105 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 106 do_test autoindex1-211 { | |
| 107 db status step | |
| 108 } {7} | |
| 109 do_test autoindex1-212 { | |
| 110 db status autoindex | |
| 111 } {7} | |
| 112 | |
| 113 | |
| 114 # Modify the second table of the join while the join is in progress | |
| 115 # | |
| 116 do_execsql_test autoindex1-299 { | |
| 117 UPDATE sqlite_stat1 SET stat='10000' WHERE tbl='t2'; | |
| 118 ANALYZE sqlite_master; | |
| 119 EXPLAIN QUERY PLAN | |
| 120 SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a); | |
| 121 } {/AUTOMATIC COVERING INDEX/} | |
| 122 do_test autoindex1-300 { | |
| 123 set r {} | |
| 124 db eval {SELECT b, d FROM t1 CROSS JOIN t2 ON (c=a)} { | |
| 125 lappend r $b $d | |
| 126 db eval {UPDATE t2 SET d=d+1} | |
| 127 } | |
| 128 set r | |
| 129 } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988} | |
| 130 do_test autoindex1-310 { | |
| 131 db eval {SELECT d FROM t2 ORDER BY d} | |
| 132 } {919 930 941 952 963 974 985 996} | |
| 133 | |
| 134 # The next test does a 10-way join on unindexed tables. Without | |
| 135 # automatic indices, the join will take a long time to complete. | |
| 136 # With automatic indices, it should only take about a second. | |
| 137 # | |
| 138 do_test autoindex1-400 { | |
| 139 db eval { | |
| 140 CREATE TABLE t4(a, b); | |
| 141 INSERT INTO t4 VALUES(1,2); | |
| 142 INSERT INTO t4 VALUES(2,3); | |
| 143 } | |
| 144 for {set n 2} {$n<4096} {set n [expr {$n+$n}]} { | |
| 145 db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4} | |
| 146 } | |
| 147 db eval { | |
| 148 SELECT count(*) FROM t4; | |
| 149 } | |
| 150 } {4096} | |
| 151 do_test autoindex1-401 { | |
| 152 db eval { | |
| 153 SELECT count(*) | |
| 154 FROM t4 AS x1 | |
| 155 JOIN t4 AS x2 ON x2.a=x1.b | |
| 156 JOIN t4 AS x3 ON x3.a=x2.b | |
| 157 JOIN t4 AS x4 ON x4.a=x3.b | |
| 158 JOIN t4 AS x5 ON x5.a=x4.b | |
| 159 JOIN t4 AS x6 ON x6.a=x5.b | |
| 160 JOIN t4 AS x7 ON x7.a=x6.b | |
| 161 JOIN t4 AS x8 ON x8.a=x7.b | |
| 162 JOIN t4 AS x9 ON x9.a=x8.b | |
| 163 JOIN t4 AS x10 ON x10.a=x9.b; | |
| 164 } | |
| 165 } {4087} | |
| 166 | |
| 167 # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08 | |
| 168 # Make sure automatic indices are not created for the RHS of an IN expression | |
| 169 # that is not a correlated subquery. | |
| 170 # | |
| 171 do_execsql_test autoindex1-500 { | |
| 172 CREATE TABLE t501(a INTEGER PRIMARY KEY, b); | |
| 173 CREATE TABLE t502(x INTEGER PRIMARY KEY, y); | |
| 174 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t501',null,'1000000'); | |
| 175 INSERT INTO sqlite_stat1(tbl,idx,stat) VALUES('t502',null,'1000'); | |
| 176 ANALYZE sqlite_master; | |
| 177 EXPLAIN QUERY PLAN | |
| 178 SELECT b FROM t501 | |
| 179 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); | |
| 180 } { | |
| 181 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 182 0 0 0 {EXECUTE LIST SUBQUERY 1} | |
| 183 1 0 0 {SCAN TABLE t502} | |
| 184 } | |
| 185 do_execsql_test autoindex1-501 { | |
| 186 EXPLAIN QUERY PLAN | |
| 187 SELECT b FROM t501 | |
| 188 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); | |
| 189 } { | |
| 190 0 0 0 {SCAN TABLE t501} | |
| 191 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} | |
| 192 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)} | |
| 193 } | |
| 194 do_execsql_test autoindex1-502 { | |
| 195 EXPLAIN QUERY PLAN | |
| 196 SELECT b FROM t501 | |
| 197 WHERE t501.a=123 | |
| 198 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); | |
| 199 } { | |
| 200 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 201 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} | |
| 202 1 0 0 {SCAN TABLE t502} | |
| 203 } | |
| 204 | |
| 205 | |
| 206 # The following code checks a performance regression reported on the | |
| 207 # mailing list on 2010-10-19. The problem is that the nRowEst field | |
| 208 # of ephermeral tables was not being initialized correctly and so no | |
| 209 # automatic index was being created for the emphemeral table when it was | |
| 210 # used as part of a join. | |
| 211 # | |
| 212 do_execsql_test autoindex1-600 { | |
| 213 CREATE TABLE flock_owner( | |
| 214 owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY, | |
| 215 flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no), | |
| 216 owner_person_id INTEGER NOT NULL REFERENCES person (person_id), | |
| 217 owner_change_date TEXT, last_changed TEXT NOT NULL, | |
| 218 CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date) | |
| 219 ); | |
| 220 CREATE TABLE sheep ( | |
| 221 Sheep_No char(7) NOT NULL, | |
| 222 Date_of_Birth char(8), | |
| 223 Sort_DoB text, | |
| 224 Flock_Book_Vol char(2), | |
| 225 Breeder_No char(6), | |
| 226 Breeder_Person integer, | |
| 227 Originating_Flock char(6), | |
| 228 Registering_Flock char(6), | |
| 229 Tag_Prefix char(9), | |
| 230 Tag_No char(15), | |
| 231 Sort_Tag_No integer, | |
| 232 Breeders_Temp_Tag char(15), | |
| 233 Sex char(1), | |
| 234 Sheep_Name char(32), | |
| 235 Sire_No char(7), | |
| 236 Dam_No char(7), | |
| 237 Register_Code char(1), | |
| 238 Colour char(48), | |
| 239 Colour_Code char(2), | |
| 240 Pattern_Code char(8), | |
| 241 Horns char(1), | |
| 242 Litter_Size char(1), | |
| 243 Coeff_of_Inbreeding real, | |
| 244 Date_of_Registration text, | |
| 245 Date_Last_Changed text, | |
| 246 UNIQUE(Sheep_No)); | |
| 247 CREATE INDEX fo_flock_no_index | |
| 248 ON flock_owner (flock_no); | |
| 249 CREATE INDEX fo_owner_change_date_index | |
| 250 ON flock_owner (owner_change_date); | |
| 251 CREATE INDEX fo_owner_person_id_index | |
| 252 ON flock_owner (owner_person_id); | |
| 253 CREATE INDEX sheep_org_flock_index | |
| 254 ON sheep (originating_flock); | |
| 255 CREATE INDEX sheep_reg_flock_index | |
| 256 ON sheep (registering_flock); | |
| 257 EXPLAIN QUERY PLAN | |
| 258 SELECT x.sheep_no, x.registering_flock, x.date_of_registration | |
| 259 FROM sheep x LEFT JOIN | |
| 260 (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id, | |
| 261 s.date_of_registration, prev.owner_change_date | |
| 262 FROM sheep s JOIN flock_owner prev ON s.registering_flock = | |
| 263 prev.flock_no | |
| 264 AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00') | |
| 265 WHERE NOT EXISTS | |
| 266 (SELECT 'x' FROM flock_owner later | |
| 267 WHERE prev.flock_no = later.flock_no | |
| 268 AND later.owner_change_date > prev.owner_change_date | |
| 269 AND later.owner_change_date <= s.date_of_registration||' 00:00:00') | |
| 270 ) y ON x.sheep_no = y.sheep_no | |
| 271 WHERE y.sheep_no IS NULL | |
| 272 ORDER BY x.registering_flock; | |
| 273 } { | |
| 274 1 0 0 {SCAN TABLE sheep AS s} | |
| 275 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_own
er_1 (flock_no=? AND owner_change_date<?)} | |
| 276 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} | |
| 277 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex
_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?)} | |
| 278 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index} | |
| 279 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?)} | |
| 280 } | |
| 281 | |
| 282 | |
| 283 do_execsql_test autoindex1-700 { | |
| 284 CREATE TABLE t5(a, b, c); | |
| 285 EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c; | |
| 286 } { | |
| 287 0 0 0 {SCAN TABLE t5} | |
| 288 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 289 } | |
| 290 | |
| 291 # The following checks a performance issue reported on the sqlite-dev | |
| 292 # mailing list on 2013-01-10 | |
| 293 # | |
| 294 do_execsql_test autoindex1-800 { | |
| 295 CREATE TABLE accounts( | |
| 296 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 297 account_name TEXT, | |
| 298 account_type TEXT, | |
| 299 data_set TEXT | |
| 300 ); | |
| 301 CREATE TABLE data( | |
| 302 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 303 package_id INTEGER REFERENCES package(_id), | |
| 304 mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL, | |
| 305 raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL, | |
| 306 is_read_only INTEGER NOT NULL DEFAULT 0, | |
| 307 is_primary INTEGER NOT NULL DEFAULT 0, | |
| 308 is_super_primary INTEGER NOT NULL DEFAULT 0, | |
| 309 data_version INTEGER NOT NULL DEFAULT 0, | |
| 310 data1 TEXT, | |
| 311 data2 TEXT, | |
| 312 data3 TEXT, | |
| 313 data4 TEXT, | |
| 314 data5 TEXT, | |
| 315 data6 TEXT, | |
| 316 data7 TEXT, | |
| 317 data8 TEXT, | |
| 318 data9 TEXT, | |
| 319 data10 TEXT, | |
| 320 data11 TEXT, | |
| 321 data12 TEXT, | |
| 322 data13 TEXT, | |
| 323 data14 TEXT, | |
| 324 data15 TEXT, | |
| 325 data_sync1 TEXT, | |
| 326 data_sync2 TEXT, | |
| 327 data_sync3 TEXT, | |
| 328 data_sync4 TEXT | |
| 329 ); | |
| 330 CREATE TABLE mimetypes( | |
| 331 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 332 mimetype TEXT NOT NULL | |
| 333 ); | |
| 334 CREATE TABLE raw_contacts( | |
| 335 _id INTEGER PRIMARY KEY AUTOINCREMENT, | |
| 336 account_id INTEGER REFERENCES accounts(_id), | |
| 337 sourceid TEXT, | |
| 338 raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0, | |
| 339 version INTEGER NOT NULL DEFAULT 1, | |
| 340 dirty INTEGER NOT NULL DEFAULT 0, | |
| 341 deleted INTEGER NOT NULL DEFAULT 0, | |
| 342 contact_id INTEGER REFERENCES contacts(_id), | |
| 343 aggregation_mode INTEGER NOT NULL DEFAULT 0, | |
| 344 aggregation_needed INTEGER NOT NULL DEFAULT 1, | |
| 345 custom_ringtone TEXT, | |
| 346 send_to_voicemail INTEGER NOT NULL DEFAULT 0, | |
| 347 times_contacted INTEGER NOT NULL DEFAULT 0, | |
| 348 last_time_contacted INTEGER, | |
| 349 starred INTEGER NOT NULL DEFAULT 0, | |
| 350 display_name TEXT, | |
| 351 display_name_alt TEXT, | |
| 352 display_name_source INTEGER NOT NULL DEFAULT 0, | |
| 353 phonetic_name TEXT, | |
| 354 phonetic_name_style TEXT, | |
| 355 sort_key TEXT, | |
| 356 sort_key_alt TEXT, | |
| 357 name_verified INTEGER NOT NULL DEFAULT 0, | |
| 358 sync1 TEXT, | |
| 359 sync2 TEXT, | |
| 360 sync3 TEXT, | |
| 361 sync4 TEXT, | |
| 362 sync_uid TEXT, | |
| 363 sync_version INTEGER NOT NULL DEFAULT 1, | |
| 364 has_calendar_event INTEGER NOT NULL DEFAULT 0, | |
| 365 modified_time INTEGER, | |
| 366 is_restricted INTEGER DEFAULT 0, | |
| 367 yp_source TEXT, | |
| 368 method_selected INTEGER DEFAULT 0, | |
| 369 custom_vibration_type INTEGER DEFAULT 0, | |
| 370 custom_ringtone_path TEXT, | |
| 371 message_notification TEXT, | |
| 372 message_notification_path TEXT | |
| 373 ); | |
| 374 CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1); | |
| 375 CREATE INDEX data_raw_contact_id ON data (raw_contact_id); | |
| 376 CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype); | |
| 377 CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key); | |
| 378 CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt); | |
| 379 CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id); | |
| 380 CREATE INDEX raw_contacts_source_id_account_id_index | |
| 381 ON raw_contacts (sourceid, account_id); | |
| 382 ANALYZE sqlite_master; | |
| 383 INSERT INTO sqlite_stat1 | |
| 384 VALUES('raw_contacts','raw_contact_sort_key2_index','1600 4'); | |
| 385 INSERT INTO sqlite_stat1 | |
| 386 VALUES('raw_contacts','raw_contact_sort_key1_index','1600 4'); | |
| 387 INSERT INTO sqlite_stat1 | |
| 388 VALUES('raw_contacts','raw_contacts_source_id_account_id_index', | |
| 389 '1600 1600 1600'); | |
| 390 INSERT INTO sqlite_stat1 | |
| 391 VALUES('raw_contacts','raw_contacts_contact_id_index','1600 1'); | |
| 392 INSERT INTO sqlite_stat1 VALUES('mimetypes','mime_type','12 1'); | |
| 393 INSERT INTO sqlite_stat1 | |
| 394 VALUES('data','data_mimetype_data1_index','9819 2455 3'); | |
| 395 INSERT INTO sqlite_stat1 VALUES('data','data_raw_contact_id','9819 7'); | |
| 396 INSERT INTO sqlite_stat1 VALUES('accounts',NULL,'1'); | |
| 397 DROP TABLE IF EXISTS sqlite_stat3; | |
| 398 ANALYZE sqlite_master; | |
| 399 | |
| 400 EXPLAIN QUERY PLAN | |
| 401 SELECT * FROM | |
| 402 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) | |
| 403 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) | |
| 404 JOIN accounts ON (raw_contacts.account_id=accounts._id) | |
| 405 WHERE mimetype_id=10 AND data14 IS NOT NULL; | |
| 406 } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} | |
| 407 do_execsql_test autoindex1-801 { | |
| 408 EXPLAIN QUERY PLAN | |
| 409 SELECT * FROM | |
| 410 data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) | |
| 411 JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) | |
| 412 JOIN accounts ON (raw_contacts.account_id=accounts._id) | |
| 413 WHERE mimetypes._id=10 AND data14 IS NOT NULL; | |
| 414 } {/SEARCH TABLE data .*SEARCH TABLE raw_contacts/} | |
| 415 | |
| 416 # Another test case from an important user of SQLite. The key feature of | |
| 417 # this test is that the "aggindex" subquery should make use of an | |
| 418 # automatic index. If it does, the query is fast. If it does not, the | |
| 419 # query is deathly slow. It worked OK in 3.7.17 but started going slow | |
| 420 # with version 3.8.0. The problem was fixed for 3.8.7 by reducing the | |
| 421 # cost estimate for automatic indexes on views and subqueries. | |
| 422 # | |
| 423 db close | |
| 424 forcedelete test.db | |
| 425 sqlite3 db test.db | |
| 426 do_execsql_test autoindex1-900 { | |
| 427 CREATE TABLE messages (ROWID INTEGER PRIMARY KEY AUTOINCREMENT, message_id, do
cument_id BLOB, in_reply_to, remote_id INTEGER, sender INTEGER, subject_prefix,
subject INTEGER, date_sent INTEGER, date_received INTEGER, date_created INTEGER,
date_last_viewed INTEGER, mailbox INTEGER, remote_mailbox INTEGER, original_mai
lbox INTEGER, flags INTEGER, read, flagged, size INTEGER, color, encoding, type
INTEGER, pad, conversation_id INTEGER DEFAULT -1, snippet TEXT DEFAULT NULL, fuz
zy_ancestor INTEGER DEFAULT NULL, automated_conversation INTEGER DEFAULT 0, root
_status INTEGER DEFAULT -1, conversation_position INTEGER DEFAULT -1); | |
| 428 CREATE INDEX date_index ON messages(date_received); | |
| 429 CREATE INDEX date_last_viewed_index ON messages(date_last_viewed); | |
| 430 CREATE INDEX date_created_index ON messages(date_created); | |
| 431 CREATE INDEX message_message_id_mailbox_index ON messages(message_id, mailbox)
; | |
| 432 CREATE INDEX message_document_id_index ON messages(document_id); | |
| 433 CREATE INDEX message_read_index ON messages(read); | |
| 434 CREATE INDEX message_flagged_index ON messages(flagged); | |
| 435 CREATE INDEX message_mailbox_index ON messages(mailbox, date_received); | |
| 436 CREATE INDEX message_remote_mailbox_index ON messages(remote_mailbox, remote_i
d); | |
| 437 CREATE INDEX message_type_index ON messages(type); | |
| 438 CREATE INDEX message_conversation_id_conversation_position_index ON messages(c
onversation_id, conversation_position); | |
| 439 CREATE INDEX message_fuzzy_ancestor_index ON messages(fuzzy_ancestor); | |
| 440 CREATE INDEX message_subject_fuzzy_ancestor_index ON messages(subject, fuzzy_a
ncestor); | |
| 441 CREATE INDEX message_sender_subject_automated_conversation_index ON messages(s
ender, subject, automated_conversation); | |
| 442 CREATE INDEX message_sender_index ON messages(sender); | |
| 443 CREATE INDEX message_root_status ON messages(root_status); | |
| 444 CREATE TABLE subjects (ROWID INTEGER PRIMARY KEY, subject COLLATE RTRIM, norma
lized_subject COLLATE RTRIM); | |
| 445 CREATE INDEX subject_subject_index ON subjects(subject); | |
| 446 CREATE INDEX subject_normalized_subject_index ON subjects(normalized_subject); | |
| 447 CREATE TABLE addresses (ROWID INTEGER PRIMARY KEY, address COLLATE NOCASE, com
ment, UNIQUE(address, comment)); | |
| 448 CREATE INDEX addresses_address_index ON addresses(address); | |
| 449 CREATE TABLE mailboxes (ROWID INTEGER PRIMARY KEY, url UNIQUE, total_count INT
EGER DEFAULT 0, unread_count INTEGER DEFAULT 0, unseen_count INTEGER DEFAULT 0,
deleted_count INTEGER DEFAULT 0, unread_count_adjusted_for_duplicates INTEGER DE
FAULT 0, change_identifier, source INTEGER, alleged_change_identifier); | |
| 450 CREATE INDEX mailboxes_source_index ON mailboxes(source); | |
| 451 CREATE TABLE labels (ROWID INTEGER PRIMARY KEY, message_id INTEGER NOT NULL, m
ailbox_id INTEGER NOT NULL, UNIQUE(message_id, mailbox_id)); | |
| 452 CREATE INDEX labels_message_id_mailbox_id_index ON labels(message_id, mailbox_
id); | |
| 453 CREATE INDEX labels_mailbox_id_index ON labels(mailbox_id); | |
| 454 | |
| 455 explain query plan | |
| 456 SELECT messages.ROWID, | |
| 457 messages.message_id, | |
| 458 messages.remote_id, | |
| 459 messages.date_received, | |
| 460 messages.date_sent, | |
| 461 messages.flags, | |
| 462 messages.size, | |
| 463 messages.color, | |
| 464 messages.date_last_viewed, | |
| 465 messages.subject_prefix, | |
| 466 subjects.subject, | |
| 467 sender.comment, | |
| 468 sender.address, | |
| 469 NULL, | |
| 470 messages.mailbox, | |
| 471 messages.original_mailbox, | |
| 472 NULL, | |
| 473 NULL, | |
| 474 messages.type, | |
| 475 messages.document_id, | |
| 476 sender, | |
| 477 NULL, | |
| 478 messages.conversation_id, | |
| 479 messages.conversation_position, | |
| 480 agglabels.labels | |
| 481 FROM mailboxes AS mailbox | |
| 482 JOIN messages ON mailbox.ROWID = messages.mailbox | |
| 483 LEFT OUTER JOIN subjects ON messages.subject = subjects.ROWID | |
| 484 LEFT OUTER JOIN addresses AS sender ON messages.sender = sender.ROWID | |
| 485 LEFT OUTER JOIN ( | |
| 486 SELECT message_id, group_concat(mailbox_id) as labels | |
| 487 FROM labels GROUP BY message_id | |
| 488 ) AS agglabels ON messages.ROWID = agglabels.message_id | |
| 489 WHERE (mailbox.url = 'imap://email.app@imap.gmail.com/%5BGmail%5D/All%20Mail') | |
| 490 AND (messages.ROWID IN ( | |
| 491 SELECT labels.message_id | |
| 492 FROM labels JOIN mailboxes ON labels.mailbox_id = mailboxes.ROWID | |
| 493 WHERE mailboxes.url = 'imap://email.app@imap.gmail.com/INBOX')) | |
| 494 AND messages.mailbox in (6,12,18,24,30,36,42,1,7,13,19,25,31,37,43,2,8, | |
| 495 14,20,26,32,38,3,9,15,21,27,33,39,4,10,16,22,28, | |
| 496 34,40,5,11,17,23,35,41) | |
| 497 ORDER BY date_received DESC; | |
| 498 } {/agglabels USING AUTOMATIC COVERING INDEX/} | |
| 499 | |
| 500 # A test case for VIEWs | |
| 501 # | |
| 502 do_execsql_test autoindex1-901 { | |
| 503 CREATE TABLE t1(x INTEGER PRIMARY KEY, y, z); | |
| 504 CREATE TABLE t2(a, b); | |
| 505 CREATE VIEW agg2 AS SELECT a, sum(b) AS m FROM t2 GROUP BY a; | |
| 506 EXPLAIN QUERY PLAN | |
| 507 SELECT t1.z, agg2.m | |
| 508 FROM t1 JOIN agg2 ON t1.y=agg2.m | |
| 509 WHERE t1.x IN (1,2,3); | |
| 510 } {/USING AUTOMATIC COVERING INDEX/} | |
| 511 | |
| 512 | |
| 513 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
