title: How to reduce the size of an sqlite3 database for iphone?
body: <p><em>edit: many thanks for all the answers. Here are the results after applying the optimisations so far:</em></p>
  
  <ul>
  <li><em>Switching to sorting the characters and run length encoding - new DB size 42M</em></li>
  <li><em>Dropping the indexes on the booleans - new DB size 33M</em></li>
  </ul>
  
  <p><em>The really nice part is this hasn't required any changes in the iphone code</em></p>
  
  <p>I have an iphone application with a large dictionary held in sqlite format (read only). I'm looking for ideas to reduce the size of the DB file, which is currently very large.</p>
  
  <p>Here is the number of entries and resulting size of the sqlite DB:</p>
  
  <pre><code>franks-macbook:DictionaryMaker frank$ ls -lh dictionary.db
  -rw-r--r--  1 frank  staff    59M  8 Oct 23:08 dictionary.db
  franks-macbook:DictionaryMaker frank$ wc -l dictionary.txt
    453154 dictionary.txt
  </code></pre>
  
  <p>...an average of about 135 bytes per entry.</p>
  
  <p>Here is my DB schema:</p>
  
  <pre><code>create table words (word text primary key, sowpods boolean, twl boolean, signature text)
  create index sowpods_idx on words(sowpods)
  create index twl_idx on words(twl)
  create index signature_idx on words(signature)
  </code></pre>
  
  <p>Here is some sample data:</p>
  
  <pre><code>photoengrave|1|1|10002011000001210101010000
  photoengraved|1|1|10012011000001210101010000
  photoengraver|1|1|10002011000001210201010000
  photoengravers|1|1|10002011000001210211010000
  photoengraves|1|1|10002011000001210111010000
  photoengraving|1|1|10001021100002210101010000
  </code></pre>
  
  <p>The last field represents the letter frequencies for anagram retrieval (each position is in the range 0..9). The two booleans represent sub dictionaries.</p>
  
  <p>I need to do queries such as:</p>
  
  <pre><code>select signature from words where word = 'foo'
  select word from words where signature = '10001021100002210101010000' order by word asc
  select word from words where word like 'foo' order by word asc
  select word from words where word = 'foo' and (sowpods='1' or twl='1')
  </code></pre>
  
  <p>One idea I have is to encode the letter frequencies more efficiently, e.g. binary encode them as a blob (perhaps with RLE as there are many zeros?). Any ideas for how best to achieve this, or other ideas to reduce the size? I am building the DB in ruby, and reading it on the phone in objective C. </p>
  
  <p>Also is there any way to get stats on the DB so I can see what is using the most space?</p>