w b uttered:
Hi all,
Just had a quick question with regards to the order of the columns
within a create table statement
I have a few tables that use the BLOB type for storing various lengths
of binary data and I was wondering if its better (more efficient) to
always declare columns of this type last within the create table
statement or not.
I'm not searching on these columns but they do have the chance of being
updated with more or less binary data than was originally in them, so
wondered if there is any penalty difference for having them at the end or
in the middle of of a list of other columns within the table. Or does
it not really matter given that any column can handle any data type ?
Put longer and not searched for columns at the end of the column list.
SQLite will put a minimum of 4 rows in a single page, which for 1K pages,
results in something like <240 bytes per row of room after meta
information has been used. For rows bigger than this, SQLite builds an
overflow list of pages, into which the rest of the data is written. This
overflow list is slow to traverse, so it is better to have indexed and/or
commonly used columns in the first couple of hundred bytes of the row to
avoid having to traverse the overflow pages.
Thanks
Wayne
Christian
--
/"\
\ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
