SQLite compresses rows before storing and decompresses rows before returning fields. BLOB fields are the most time consuming to process and so should be placed at the end of the row. Often used fields - i.e. (foreign) key fields - should be placed at the front of the row. This will help most if your select field list is limited to the fields you actually need instead of "*".
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Jeff Archer Gesendet: Donnerstag, 06. Oktober 2016 15:46 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] Order of fields for insert Just a quick question. I am actually deciding if I need to do some performance testing of this but thought I might gain some quick insight. My specific insert and table are below but really I am looking for a general answer to the question not just this specific case. Are there any performance or other considerations of the order of the fields for an insert? Are the following equivalent? regardless of number of values inserting? regardless of size of the data being inserted? INSERT INTO mytable( wid1,cnt, dat, wid3,wid2) VALUES (?,?,?,?) - VS - INSERT INTO mytable( wid1,wid2,wid3,cnt ,dat ) VALUES (?,?,?,?) CREATE TABLE mytable ( id INTEGER PRIMARY KEY AUTOINCREMENT" , wid1 INTEGER REFERENCES othertable ( id ) ON DELETE CASCADE , wid2 INTEGER REFERENCES othertable ( id ) ON DELETE CASCADE , wid3 INTEGER REFERENCES othertable ( id ) ON DELETE CASCADE , cnt INTEGER DEFAULT 1 ,dat TEXT ) Jeff _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
