Ah, ha! I had actually originally planned on doing that but when I read the primary key could only be an integer I assumed it was a 32 bit integer so I would need a separate column for the date. But you're right of course and I see now that as of version 3, I can use 64 bit integers as the primary key! This brings my database file down to a very reasonable 513024 bytes and speeds up my code to boot.
What's more, since nearly all the prices in my data can be represented in 6 characters or less, I found I can save even more space by storing them as strings like this: CREATE TABLE StockName (date INTEGER PRIMARY KEY, price TEXT); This brings my test database file size down to the bargain-basement size of 431104 bytes! Right on par with my text file format (which I will no longer be needing). Thanks a lot guys! Corey On Fri, Jul 18, 2008 at 1:32 PM, Filip Navara <[EMAIL PROTECTED]> wrote: > Not really two copies as the integer could be primary key ... > something along the lines of > > CREATE TABLE StockName (date INTEGER PRIMARY KEY, price REAL); > > Regards, > F. > > On Fri, Jul 18, 2008 at 10:03 PM, Jay A. Kreibich <[EMAIL PROTECTED]> wrote: > > On Fri, Jul 18, 2008 at 11:58:16AM -0700, Corey Nelson scratched on the > wall: > >> > sqlite3 Ticks.db ".dump" > >> BEGIN TRANSACTION; > >> CREATE TABLE StockName (date TEXT UNIQUE ON CONFLICT REPLACE, price > REAL); > > > >> I would expect the database file to store a bit of "extra" data but it's > >> 2.17 times bigger than the text file! > > > > As others have pointed out, the issue is with the index, which is > > created automatically by the "UNIQUE" constraint. > > > > In SQLite an index holds a full copy of the data. Since the vast > > majority of your data (byte wise) is the date string, slightly more > > than 2x sounds just about right. > > > > You can try to convert the date to a large integer. SQLite supports > > up to 64 bit ints (signed), which should hold your current > > representation without problems. You'll still have two copies, but > > the data should be much smaller. > > > > -j > > > > -- > > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > > > "'People who live in bamboo houses should not throw pandas.' Jesus said > that." > > - "The Ninja", www.AskANinja.com, "Special Delivery 10: Pop!Tech 2006" > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
