On Thu, Mar 7, 2013 at 9:50 AM, Dominique Devienne <ddevie...@gmail.com> wrote: > On Tue, Mar 5, 2013 at 4:05 PM, Richard Hipp <d...@sqlite.org> wrote: > > 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. > > This information sparked a bit of a debate among my colleagues, because since SQLite's back-end is paged, some of them assumed that pages corresponding (entirely) to un-selected (blob) columns would be skipped/seeked over, thus avoiding to be read from disk, even if followed by other columns that are selected. > > Am I reading you correctly that this assumption is incorrect?
I guess I can answer that question myself, given the experiment below. Selecting only from the row's head is fast (under the 15ms clock tick here), including selecting the blob's length (or typeof, as you wrote). Selecting the rows big blob or just the tail is slow, likely because reading the whole row. Selecting just leading bytes of the big blob is slow too, although logically that's still a row prefix. I guess the engine doesn't "peek" at substr(big, 0, N) to realize that's still a row prefix, and ends up reading the whole row (maybe minus the tail, although that makes no difference), even though only 10 bytes are requested. Very informative. Thanks, --DD C:\Users\DDevienne>sqlite3 toseekornottoseek.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table t (head number, big blob, tail text); sqlite> insert into t values (1, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (2, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (3, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (4, randomblob(100*1024*1024), "100 MB"); sqlite> insert into t values (5, randomblob(100*1024*1024), "100 MB"); sqlite> .q C:\Users\DDevienne>sqlite3 toseekornottoseek.db SQLite version 3.7.15.2 2013-01-09 11:53:05 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .timer ON sqlite> select head from t; 1 2 3 4 5 CPU Time: user 0.000000 sys 0.000000 sqlite> select head, tail from t; 1|100 MB 2|100 MB 3|100 MB 4|100 MB 5|100 MB CPU Time: user 0.280802 sys 1.528810 sqlite> select tail from t; 100 MB 100 MB 100 MB 100 MB 100 MB CPU Time: user 0.156001 sys 1.653611 sqlite> select length(big) from t; 104857600 104857600 104857600 104857600 104857600 CPU Time: user 0.000000 sys 0.000000 sqlite> select hex(substr(big, 34*1024*1024, 10)) from t; D6DB92922E2E9FF91612 5DBD2F8E31B9DCE47BA3 EAE710AAC1D92AA8BE63 B9D549292AB82EBA21BD CC979172DFA70D8A4420 CPU Time: user 0.343202 sys 1.809612 sqlite> select hex(substr(big, 0, 10)) from t; DCA56B817BEF685AED 21D8D8D157AE5DDC3F 723BC3489CBA8146FF 1E31DF8816D2DD6E6D 4514A82E23F1BE638E CPU Time: user 0.374402 sys 1.762811 sqlite> select typeof(big) from t; blob blob blob blob blob CPU Time: user 0.000000 sys 0.015600 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
