Read Mr. Hipp's reply to me when I asked a similar question:
"Two things to be aware of: (1) When reading a row, SQLite reads from beginning to end. So if you have some small integer or boolean fields, it is better to put them first in the table. Otherwise, SQLite has to read past the big BLOBs in order to get to the smaller fields, even if the BLOBs themselves are not used. (2) When changing any column of a row, the entire row is rewritten, including the unchanged columns. So if you have some smaller fields (integers and booleans) that change frequently and also some large BLOBs that change infrequently, you might consider factoring the BLOBs out into a separate table just so they don't have to be rewritten every time a boolean in the same row changes. Both points above a purely performance considerations. You should always get the correct answer either way." Here's the entire discussion: http://sqlite.1065341.n5.nabble.com/BLOBs-and-NULLs-td75201.html -- Bill Drago Senior Engineer L3 Narda-MITEQ 435 Moreland Road Hauppauge, NY 11788 631-272-5947 / William.Drago at L-3COM.com > -----Original Message----- > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite- > users-bounces at mailinglists.sqlite.org] On Behalf Of Pol-Online > Sent: Thursday, April 30, 2015 12:45 PM > To: sqlite-users at mailinglists.sqlite.org > Subject: [sqlite] Does column order matter for performance? > > Hi, > > I wasn?t able to find the answer to this question online: does the > column order matter for SQLite performance? E.g. should you put fixed > width columns like INTEGER before TEXT or BLOB? > > > -Pol > > ________________________________ > Pol-Online > info at pol-online.net (mailto:info at pol-online.net) > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any attachments are solely for the use of the addressee and may contain information that is privileged or confidential. Any disclosure, use or distribution of the information contained herein is prohibited. In the event this e-mail contains technical data within the definition of the International Traffic in Arms Regulations or Export Administration Regulations, it is subject to the export control laws of the U.S.Government. The recipient should check this e-mail and any attachments for the presence of viruses as L-3 does not accept any liability associated with the transmission of this e-mail. If you have received this communication in error, please notify the sender by reply e-mail and immediately delete this message and any attachments.
