Jacob Engstrand wrote: > > On 2004-06-17, at 16.39, [EMAIL PROTECTED] wrote: > >> If you have a large record size, then it also helps tremendously to put the >> indexed columns first in the row (I had a table with large blob data that >> was terribly slow to query until I moved the small, non-blob columns >> first). [That was found out by trial and error] >> > > > Really!? Let's see if I understand you correctly: Your SELECT statement is > exactly the same, but you just changed the order of the columns in one table, > and all of a sudden your SELECT query performed better? >
SQLite stores data in column order. The first column is stored first, the second column second, and so forth. The first 236 bytes of data are stored directly on the B-Tree pages. Any information beyond the first 236 bytes is stored on separate overflow pages.
So if you are doing a query that requires checking the value of some column whose data appears past the 236 byte mark then overflow pages will need to be read from disk for each row examined, even if the test turns out to be false and the content of that row never makes it into the result set. Reading all those extra overflow pages takes time.
The 236-byte figure is for SQLite version 2.8. Version 3.0 will store up to 1000 bytes or maybe a little more before resorting to using an overflow page - and even more than that if you choose a page size larger than 1024 bytes.
-- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
