It seemed strange that a simple "select * from table" that I was doing was so
slow. The table contained about 20 columns (fields) and 300 rows. The select
took about 1.5 seconds. (using SQLite Expert).
The table contained a blob field, with a "fair" amount of data spread over the
rows (max was around 6 MB...). The sqlite file was about 40 MB.
After some testing, I discovered that the problem was caused by the blob field
being in the middle of the columns (e.g. column 8). Results were slow even if I
didn't include the blob field in the select. If I moved this blob field to the
end of the table (i.e. last column), then select was very fast if I didn't
include the last field (140ms). If I did include the last field (i.e. the blob
field), it was slower (400ms) but still significantly faster that results
above, when blob field was in the middle of the table.
So my questions:
Is this standard behaviour for sql databases? (that I have only found out now).
Is there actually some way to bypass this "problem" (e.g. database setting..)?
Is it generally advisable to separate out blob fields into their own table to
ensure fast select speeds?
Thanks
Rael Bauer
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
