On 5 Mar 2012, at 12:16pm, Larry Knibb <larry.kn...@gmail.com> wrote:
> Thanks for the quick reply, Simon. I was rather hoping there might be > some efficiency tweaks I can do to the db file (e.g. more explicit > creation statements, or postprocessing) instead of getting into custom > sqlite builds. [snip] SQLite is about equally space-efficient for pretty-much everything you do. I have seen some reports that creating tables by INSERTing rows in primary key order makes a database smaller, but I don't remember any of the experts posting anything definite about it one way or the other. Something you might try is to build your database file however you want, then use the shell tool's '.dump' and '.read' commands to make another database file out of it. Among other things I believe this does INSERT rows in primary key order. If the two files are radically different in size then there's something strange going on and, of course, you've found a way to make your database smaller. > Can I limit possible string lengths for the TEXT fields or is it > already using VARCHAR internally? Affinities of VARCHAR are interpreted as TEXT, with length limits being ignored. All strings in SQLite are variable-length strings. See <http://sqlite.org/datatype3.html> > If so, any idea where all the space > is being used? Can I profile the tables to learn how much data they > hold individually or the efficiency of that storage? Take a look on <http://sqlite.org/download.html> to see if there's a build of 'sqlite-analyzer' for your platform. This is a good way of getting a ton of information about your database file. You might find something weird. Generally, in cases where it /is/ possible to save a lot of space in a database, it's because people have made indexes which are unnecessary, or unnecessarily long, or duplicates of existing indexes. If you understand SQL well and made your indexes by examining your INSERT and UPDATE commands, then this probably isn't your problem. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
