On Tue, Mar 5, 2013 at 9:45 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> > On 5 Mar 2013, at 2:37pm, Richard Hipp <d...@sqlite.org> wrote: > > > On Tue, Mar 5, 2013 at 9:26 AM, Simon Slavin <slav...@bigfraud.org> > wrote: > > > >> I've seen programs written by people who always do 'SELECT *" even when > >> they want just a couple of fields. That's going to be a lot more > expensive > >> under SQLite4. Maybe it'll teach them to be better programmers. > > > > Why will it be any more expensive under SQLite4 than it is under SQLite3? > > I probably misunderstood. My impression was that under SQLite3, all the > fields of one row were stored 'near' one-another, so getting the value of > one of them would read the others into cache, whereas under SQLite4 this > wasn't true. > I think you have misunderstood too. Both SQLite3 and SQLite4 are row-oriented databases. The storage engines are very different, but they still store each row as a single big blob that embeds the individual fields. The encoding format of this blob in SQLite3 is described at ( http://www.sqlite.org/fileformat2.html#record_format) and the encoding format for this blob in SQLite4 is described at ( http://www.sqlite.org/src4/doc/trunk/www/data_encoding.wiki). The encodings are similar in philosophy but different in detail. The SQLite4 version is a little more efficient and more extensible. Both implementations allow for reading just the prefix of the content blob in order to access earlier fields of a table, leaving the tail of the blob unread on disk. So in all cases, it pays to put your frequently accessed small fields early in your table, and your infrequently accessed multi-megabyte BLOB columns at the end of the table. That way you won't have to read over a multi-megabyte BLOB just to get at the BOOLEAN value at the end. Both implementations also allow you to do things like typeof(x) and length(x) without actually reading the entire content off of disk. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
