On Sun, 15 Oct 2017 17:45:03 -0700 (MST), Fiona <cxfhn1...@gmail.com> wrote:
>>> Swapping the columns tile_data and tile_id may improve performance > significantly, especially if the BLOB can get bigger than a database page. > > Thanks for your advice, could you please explain more why is that? > The primary key is not change at all, what exectly causes the improvement? In SQLite, columns are stored in database pages in the order they are defined. The primary key of any table is usually accessed more often than any other column, becuase it is either used by a WHERE clause or the ON clause of a JOIN. If a BLOB is bigger than a page, SDQLite has to read one or more so called overflow paged to get to the primary key. 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. HTH -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
