[sqlite] More columns vs. several tables

[Felix Schwarz]

Hi,

I have to decide on a table layout for storing the data of my  
upcoming project.

Yesterday I have made my way through the excellent presentation at  
http://www.sqlite.org/php2004/page-036.html and read the sentence

"Put small and frequently used columns early in the table to minimize  
the need to follow the overflow chain."

Now, that's interesting! And I'm wondering whether there is a big  
performance hit for a simple

        SELECT binarydata FROM entries WHERE somehash = 27817298;

when I use

        CREATE TABLE entries(
                entry_id INTEGER PRIMARY KEY,
                somehash  INTEGER,
                property1 INTEGER,
                property2  VARCHAR(255),
                property3  VARCHAR(255),
                binarydata BLOB
        );

instead of splitting the binary data (around 40K each) into two  
tables like this:

        CREATE TABLE entries(
                entry_id INTEGER PRIMARY KEY,
                somehash  INTEGER,
                property1 INTEGER,
                property2  VARCHAR(255),
                property3  VARCHAR(255),
                binary_id INTEGER
        );

        CREATE TABLE binaries(
                binary_id INTEGER PRIMARY KEY,
                binarydata BLOB
        );

and then use a select of this form:

	SELECT binarydata FROM binaries WHERE binary_id = (SELECT binary_id  
FROM entries WHERE somehash = 27817298);

Also, could the usage of VIEWs speed up the SELECTing of data in the  
second example? Or does it just use SELECTs under the hood itself,  
i.e. without any caching of data?

Thanks in advance for any feedback.

Felix