On 29 Jul 2015, at 8:28pm, Jean Chevalier <jchevalier at gmx.net> wrote:
> Would it be realistic to pretend to use SQLite as a column-store database by > leveraging ALTER TABLE ... ADD COLUMN massively and to expect better > performance? Sorry. No. The data for each row is stored together. > Suppose that instead of defining a table like CREATE TABLE (key, c1, c2, ... > cn) I defined it as CREATE TABLE (key) followed by n-times "ALTER TABLE ... > ADD COLUMN", for each column. There is no difference between the results of these two sets of commands. The only difference in what would be stored in the database file would come if you inserted some rows before you added a column. And the space saved would be one octet per NULL value -- in other words, too small to worry about. > Presumably when I query the table for a /key,column/ pair, SQLite will only > need to access the storage area for /key/ and the storage area for /column/, > and will not need to read the content of all the other columns? The data for each row is stored together, in order of column definition. Columns not stored are assumed to be NULL, which allows ALTER TABLE ... ADD COLUMN to work correctly. SQLite needs to parse the entire row of data, up to and including the last column it needs to retrieve to satisfy the command. So, for example, SELECT c5 FROM myTable ORDER BY c8 Requires SQLite to parse all of c1,c2,c3,c4,c5,c6,c7,c8 for each row to find the two values it actually wants. For more on the SQLite file format see <https://www.sqlite.org/fileformat.html> Simon.
