[2008.* | Qtopia and whatever else uses Qtopia PIM] Sqlite DB speed trick

[nick d.]

Saw this on reddit the other day and thought it might help [1].

sqlite3 has a "vacuum" command which does a few nifty things to
compact your database [2] in which the example shows you can do it to
your firefox 3 sqlite db files to increase performance (in things like
the awesome bar etc). I thought I might try this on my phone as I know
the qtopia PIM suite uses sqlite3. So to do this on 2008.* (and
assuming this works on Qtopia distro):

0. make sure you have sqlite3 and lsof installed
     opkg install sqlite3 lsof

1. check your file sizes for comparison:
      ls -l ~/Applications/qtmail/qtopia_db.sqlite
      ls -l  ~/Applications/Qtopia/qtopia_db.sqlite

2. stop your xserver to remove the lock on the db files:
       /etc/init.d/xserver-nodm stop

3. check to make sure nothing is using the files
      lsof ~/Applications/qtmail/qtopia_db.sqlite
      lsof ~/Applications/Qtopia/qtopia_db.sqlite

4. Compact
     sqlite3 ~/Applications/qtmail/qtopia_db.sqlite
     sqlite3 ~/Applications/Qtopia/qtopia_db.sqlite

5. Restart X
     /etc/init.d/xserver-nodm start

6. Check file sizes again
     ls -l ~/Applications/qtmail/qtopia_db.sqlite
      ls -l  ~/Applications/Qtopia/qtopia_db.sqlite

7. ???

8. Profit!


This reduced my filesize from 195584 to 97280 for my
qtmail/qtopia_db.sqlite file. I can't work out how to test performance
other than experientially because of my lack of knowledge of qtopia
debugging. Hopefully this means less files to load in memory which
should equate to slightly quicker performance. It certainly worked for
my firefox.

 Do at your own risk! Although I'm sure that sqlite's own locking
mechanisms will stop you from nuking your db files. Good luck! And let
me know of your success (definitely not failure)! :P

-Nick


Refs

1.http://www.gettingclever.com/2008/06/vacuum-your-firefox-3.html
2. http://www.sqlite.org/lang_vacuum.html