On 16 Oct 2017, at 3:33am, Kees Nuyt <k.n...@zonnet.nl> wrote:
> In SQLite, columns are stored in database pages in the order > they are defined. [snip] > > So, it is best practice put keys and all columns with small > contents up front, and all big TEXT and BLOB columns at the end > of the column list, in ascending order of expected size. Kees’ clear answer missed an important point. When retrieving data from a row, SQLite does not bother to read past the last column it needs. So if you have CREATE TABLE MyTable (c1 INTEGER, c2 TEXT, c3 INTEGER, c4 TEXT, c5 BLOB) and do this SELECT c1, c4 FROM MyTale WHERE c2 = "HELLO" then SQLite reads from c1 to c4, because it needs c4, but it stops at c4 for every row. It never has to read any BLOB data at all. This can save a lot of time, especially if your BLOBs are long and may cross a page boundary. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
