sqlite 索引性能建议 [英] sqlite Indexing Performance Advice

问题描述

我的 iPhone 应用程序中有一个 sqlite 数据库，我可以通过 Core Data 框架访问它.我正在使用 NSPredicates 来查询数据库.

I have an sqlite database in my iPhone app that I access via the Core Data framework. I'm using NSPredicates to query the database.

我正在构建一个搜索功能，需要搜索六个不同的包含文本的 varchar 字段.目前，它非常慢，我需要提高性能，可能是在 sqlite 数据库中.最好在所有这些列上创建索引吗?或者构建一个自定义索引表将这六列扩展为多行，每行包含一个单词和它匹配的 ID 会更好吗?还有其他建议吗?

I am building a search function that needs to search six different varchar fields that contain text. At the moment, it's very slow and I need to improve performance, probably in the sqlite database. Would it be best to create an index on all those columns? Or would it be better to build a custom index table that expands those six columns into multiple rows, each containing a word and the ID it matches? Any other suggestions?

推荐答案

您可以采取一些措施来提高在 sqlite 数据库中搜索文本的性能.尽管 Core Data 将您从底层存储中抽象出来，但是当您使用 sqlite 支持您的存储时，了解正在发生的事情可能会很好.

There are things you can do to improve the performance of searching for text in sqlite databases. Although Core Data abstracts you away from the underlying store it can be good to have an appreciation of what is going on when your store is backed using sqlite.

如果我们假设您正在对这些字段进行子字符串搜索，那么您可以采取一些措施来提高搜索性能.Apple 建议使用派生属性.这相当于在您的模型中维护用于搜索的属性的规范化版本.派生属性应该以可以被索引的方式完成.然后，您可以使用二元运算符 > <= 等

If we assume you're doing a substring search of these fields there are things you can do to improve search performance. Apple recommend using a derived properties. This amounts to maintaining a normalised version of your property in your model that is used for searching. The derived property should be done in a way that it can be indexed. You then express your search in terms of this derived property using binary operators > <= etc.

我发现这样做可以将我们的搜索时间从大约 1 秒缩短到 100 毫秒以下.

I found doing this reduced our search from around 1 second to under 100ms.

为了清楚起见，我建议查看 ADC 示例 http://developer.apple.com/mac/library/samplecode/DerivedProperty/

To make things clear I would suggest looking at the ADC example http://developer.apple.com/mac/library/samplecode/DerivedProperty/

这篇关于sqlite 索引性能建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！