Do you have sqlite_stat1 or sqlite_stat4 tables present (that is, have you ever run analyze)?
Neither running a REINDEX nor a VACUUM will modify the statistics, however, dropping the index will delete the index statistics for the index (and re-creating the index will not re-create the statistics). If the statistics tables (sqlite_stat1 and/or sqlite_stat4) are present then you update them by running analyze; You can also drop the sqlite_stat1 and/or sqlite_stat4 tables to get rid of the statistics entirely, or delete individual rows from the table to get rid of stale shape data. If the sqlite_stat1 or sqlite_stat4 tables are present, then if you run ANALYZE; rather than reindexing or dropping/recreating the index, what is the result? -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of dirdi >Sent: Thursday, 29 August, 2019 08:10 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Unexpected REINDEX behavior. > >Hi there, I noticed some - at least for me - unexpected behavior of >the >REINDEX command: > >I have a DB with 7 tables, 11 indexes and a size of about 140MiB. The >DB >is generated by a python script parsing an XML file. Entries are >added >to the tables in arbitrary order. > >After the tables have been created and filled, some UPDATE queries >are >executed. One of those queries took ~36m. I tried to reduce the >runtime >by using the REINDEX command, but with no success. However, if I >delete >and recreate one of the indexes, using the DROP INDEX and CREATE >INDEX >commands, the UPDATE query takes only ~2m. > >Unfortunately I can not share the DB with you, due to legal reasons >and >was also not able to create a test script to trigger this behavior. > >The two tables, affected by the UPDATE query: >> CREATE TABLE `tbl1` ( >> `id` INTEGER PRIMARY KEY AUTOINCREMENT, >> `str` TEXT NOT NULL UNIQUE COLLATE NOCASE, >> `int` INTEGER NOT NULL, >> `tbl2_id` INTEGER, >> `other_str` TEXT DEFAULT NULL, >> `other_int1` INTEGER DEFAULT NULL, >> `other_int2` INTEGER NOT NULL DEFAULT 0, >> FOREIGN KEY(`tbl2_id`) REFERENCES `tbl2`(`id`) >> ); >> >> CREATE TABLE `tbl2` ( >> `id` INTEGER PRIMARY KEY AUTOINCREMENT, >> `str` TEXT NOT NULL UNIQUE COLLATE NOCASE, >> `int` INTEGER NOT NULL >> ); > >Their corresponding indexes: >> CREATE INDEX `idx1` ON `tbl1` ( >> `int` >> ); >> CREATE INDEX `idx2` ON `tbl1` ( >> `tbl2_id` >> ); >> CREATE INDEX `idx3` ON `tbl2` ( >> `int` >> ); >... filling the tables ... > >Recreating index idx1 (To archive the speedup, recreation of the >other >indexes is not necessary): >> DROP INDEX `idx1`; >> CREATE INDEX `idx1` ON `tbl1` ( >> `int` >> ); >And the update query: >> UPDATE `tbl1` >> SET `tbl2_id` = ( >> SELECT `tbl2`.`id` >> FROM `tbl2` >> WHERE `tbl1`.`int` >= `tbl2`.`int` >> AND `tbl1`.`str` LIKE '%' || `tbl2`.`str` >> ORDER BY `tbl2`.`int` DESC >> LIMIT 1 >> ); > >And it's corresponding query plan: >> id parent notused detail> 3 0 0 SCAN TABLE tbl1 >> 23 0 0 CORRELATED SCALAR SUBQUERY 1 >> 29 23 0 SEARCH TABLE tbl2 USING INDEX idx3 (int<?) > >tbl1 and tbl2 contain about 600k and 20k entries, respectively. > >Does one have an explanation for this behavior? >Is it expected that the REINDEX command produces other results than >"manually" recreation of an index? >If yes, under which circumstances does this happen? >And is there some way to measure the "quality" of an index / if it >has >been scattered? > >PS: I also tried the VACUUM (combined w/ and w/o REINDEX) command to >no >success. > >-- >Best regards > >dirdi >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
