On Wed, Jan 17, 2018 at 1:06 PM, Nick <[email protected]> wrote:
> What is more important is that, I think it is a better way to establish my > tables according to however the source data is. Because SQLite stores all cells in-row, including large text and blob columns, a common advise from the community is to denormalize your schema and put those large columns in a separate table. That forces you to join the tables when you want to access columns from both tables, and to manually manage the lifetime of the separate rows which are logical a single row, but then you gain much faster table scans, because you no longer have to read those large columns when going from row to row during a scan. Rows belong to pages, and when a row no longer fits into a page, "overflow" pages are used, in a forward-linked-list manner if you want, so a scan must still read all pages to find the next row on the next row-page that follows all those overflow pages (this is just a high level description from someone that does *not* know to exact details...). From this it's obvious smaller rows can avoid a log of IO and speed things up. In not using a separate table, at least put "big" columns at the end, again to avoid have to skip other them to read a small column at the end. --DD PS: There's a special mode related to AUTOVACUM where SQLite adds special pages that keep track of other pages that sometimes allow it to "jump over" pages, but that's niche and I'm not even sure it applies here. PPS: Still, the fact your query plan uses an automatically-generated (transient) index means none of the persistent indices is suspect, and your overlapping UNIQUE indices is also strange, FWIW. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
