On 8/29/19 6:00 PM, Simon Slavin wrote: > Just in case, please run an integrity-check on the database as it is before > you delete/rebuild the index.
The python script creates the DB from scratch every time. However ... > PRAGMA integrity_check; ... returned ... > ok > You might be able to learn more about the various versions of that index > using this: > > <https://sqlite.org/sqlanalyze.html> > > It may show that the various versions of idx1 you see/create take different > numbers of pages, or have different depths. However, I agree with you that > the amount of time disparity is unexpected. Here is what sqlanalyze reports about the index: > *** Index IDX1 of table TBL1 ******************************************* > > Percentage of total database...................... 3.7% > Number of entries................................. 551407 > Bytes of storage consumed......................... 5517312 > Bytes of payload.................................. 3826907 69.4% > Bytes of metadata................................. 1670381 30.3% > B-tree depth...................................... 3 > Average payload per entry......................... 6.94 > Average unused bytes per entry.................... 0.04 > Average metadata per entry........................ 3.03 > Average fanout.................................... 224.00 > Non-sequential pages.............................. 1282 95.2% > Maximum payload per entry......................... 7 > Entries that use overflow......................... 0 0.0% > Index pages used.................................. 6 > Primary pages used................................ 1341 > Overflow pages used............................... 0 > Total pages used.................................. 1347 > Unused bytes on index pages....................... 5816 23.7% > Unused bytes on primary pages..................... 14208 0.26% > Unused bytes on overflow pages.................... 0 > Unused bytes on all pages......................... 20024 0.36% I ran it twice, once before executing DROP INDEX and CREATE INDEX commands ... > $ ./sqlite3_analyzer db.sqlite3 > run1 ... and once afterwards: > $ ./sqlite3_analyzer db.sqlite3 > run2 The only difference between both runs: > $ diff run1 run2 > 1260d1259 > < INSERT INTO space_used > VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312); > 1270a1270 >> INSERT INTO space_used >> VALUES('idx_words1','words',1,0,551407,550067,3,3826907,0,0,7,6,1341,0,5816,14208,0,1282,5517312); -- Best regards dirdi _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
