On Sat, Jan 29, 2011 at 03:07:47PM -0800, Rael Bauer scratched on the wall:
> 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).
> So my questions:
> Is this standard behaviour for sql databases? (that I have only
> found out now).
No, it is specific to the way SQLite stores data on the disk.
If you're interested in the specifics, you can read about the SQLite
file format and the on-disk encoding and storing of rows.
> Is there actually some way to bypass this "problem" (e.g. database setting..)?
You'll see the slow down anytime you access anything "past" the BLOB.
To avoid that, put the BLOBs at the end of the rows and avoid "SELECT *"
style queries.
You can also just put them in a different table.
> Is it generally advisable to separate out blob fields into their own
> table to ensure fast select speeds?
Yes. If the BLOB column is not frequently accessed, and is "auxiliary"
data to the rest of the row, it is a common practice to break them
off into their own "detail" table (i.e. a one-to-one table).
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Intelligence is like underwear: it is important that you have it,
but showing it to the wrong people has the tendency to make them
feel uncomfortable." -- Angela Johnson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
