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屋！