如何减少iphone的sqlite3数据库的大小？ [英] How to reduce the size of an sqlite3 database for iphone?

问题描述

编辑：非常感谢所有的答案。以下是应用到目前为止的优化后的结果：

• 切换到排序字符和运行长度编码 - 新数据库大小42M


• 删除布尔值上的索引 - 新数据库大小33M
b
$ b




一个带有以sqlite格式（只读）持有的大字典的iphone应用程序。我正在寻找想法来减小DB文件的大小，这是目前非常大的。





这里是条目的数量和结果大小的sqlite DB：

  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 
  

...每个条目的平均值约为135字节。





/ p>



 创建表字（字文本主键，sowpods布尔值，twl布尔值，签名文本）
 create words sowpods_idx on words （sowpods）
在单词（twl）上创建索引twl_idx 
在单词（签名）上创建索引signature_idx 
  


b $ b

以下是一些示例数据：

  photoengrave | 1 | 1 | 10002011000001210101010000 
 photoengraved | 1 | 1 | 10012011000001210101010000 
 photoengraver | 1 | 1 | 10002011000001210201010000 
 photoengravers | 1 | 1 | 10002011000001210211010000 
 photoengraves | 1 | 1 | 1000 || || | | | | | | | | | | | | | | 1000 | | | | | | | | | | | 10001021100002210101010000 
  

最后一个字段表示字母检索的字母频率（每个位置都在0..9范围内）。




$ b

我需要执行以下查询：

 从词语中选择签名其中word ='foo'
从词语中选择词语其中signature ='10001021100002210101010000'order by词asc 
从词语中选择词语'foo'order by word asc 
从word ='foo'和（sowpods ='1'或twl ='1'）的单词中选择单词
  

我有一个想法是更有效地编码字母频率，例如二进制编码它们为一个blob（可能有RLE，因为有许多零？）。任何想法如何最好地实现这一点，或其他想法，以减少大小？我在ruby中构建数据库，并在目标C中的电话上读取它。





也有任何方法来获取数据库上的统计信息，使用最多的空间？

解决方案

我不清楚签名字段的所有用例，按字母顺序排列的字样将是有益的。




edit: many thanks for all the answers. Here are the results after applying the optimisations so far:

• Switching to sorting the characters and run length encoding - new DB size 42M
• Dropping the indexes on the booleans - new DB size 33M

The really nice part is this hasn't required any changes in the iphone code

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.

Here is the number of entries and resulting size of the sqlite DB:

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

...an average of about 135 bytes per entry.

Here is my DB schema:

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)

Here is some sample data:

photoengrave|1|1|10002011000001210101010000
photoengraved|1|1|10012011000001210101010000
photoengraver|1|1|10002011000001210201010000
photoengravers|1|1|10002011000001210211010000
photoengraves|1|1|10002011000001210111010000
photoengraving|1|1|10001021100002210101010000

The last field represents the letter frequencies for anagram retrieval (each position is in the range 0..9). The two booleans represent sub dictionaries.

I need to do queries such as:

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')

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.

Also is there any way to get stats on the DB so I can see what is using the most space?

解决方案

I'm not clear on all the use cases for the signature field but it seems like storing an alphabetized version of the word instead would be beneficial.

这篇关于如何减少iphone的sqlite3数据库的大小？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！