> On Feb 20, 2020, at 5:24 PM, Chip Beaulieu <chipbeaul...@gmail.com> wrote: > > CREATE INDEX `INX_Verses_BID` ON `Verses` (`BibleID`) > CREATE INDEX `INX_Verses_BID_B_C` ON `Verses` (`BibleID`,`Book`,`Chapter`) > CREATE INDEX `INX_Verses_BI_B_C_V` ON `Verses` > (`BibleID`,`Book`,`Chapter`,`VerseNumber`)
I believe the first two of those are unnecessary, since their column-lists are prefixes of the third. So removing them would buy you some performance. (Disclaimer: not a guru.) But you’ve said most of the problem is with FTS. When you update the database, does all of the indexed text really change? Or do many of the 30,000 new records contain the same text as their deleted counterparts? If the latter, you could optimize by not touching those rows. It’s also possible that dropping the FTS table before the update, and re-creating it afterwards, would be faster than incrementally changing it. —Jens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
