更好的 SQLite Select 语句性能 [英] Better performance for SQLite Select Statement

问题描述

我正在开发一个 Iphone 应用程序，用户可以在其中输入任何字符串到搜索栏中并按下搜索按钮.之后应该会出现一个结果列表.

在我的 SQLite 中，我有四列 a、b、c、d.假设它们具有以下值:

数据集 1:一:代码1b: 报告1c:描述1_1d:描述1_2数据集 2:一:代码2b:报告2c: description2_1d:描述2_2

因此，如果用户输入的值是:1_1"，那么第一个数据集将被选中，因为 clumn c.如果用户输入值:report"，则将选择第一个和第二个数据集.

因为我使用的数据库有近 60.000 个数据集，搜索部分字符串确实会降低性能.

在所有 4 列上设置索引会使 SQLite 数据库的大小变得太大.所以我根本没有使用索引.

我的选择语句如下所示:

NSString *sql = [NSString stringWithFormat:@"SELECT * FROM scode WHERE a LIKE '%@%@%@' OR c LIKE '%@%@%@' OR d LIKE '%@%@%@'", 通配符, searchBar.text, 通配符, 通配符, searchBar.text, 通配符, 通配符, searchBar.text, 通配符, 通配符, searchBar.text, 通配符];

有什么好的方法可以提高在所有列中搜索部分字符串的性能吗?

谢谢你和亲切的问候，

丹尼尔

解决方案

您正在寻找 SQLite 本身不支持的全文搜索.我对 3rd 方支持没有任何经验，但是 根据结果有几个选项.>

I'm developing an Iphone App where the user types in any string into a searchbar and presses the search button. After that a result list should appear.

In my SQLite I have four columns a, b, c, d. Let's say they have the following Values:

Dataset 1:
a: code1
b: report1
c: description1_1
d: description1_2

Dataset 2:
a: code2
b: report2
c: description2_1
d: description2_2

So if the user enters a value of: "1_1" then the first dataset will be selected because of clumn c. If the user enters a value of: "report" then the first and second dataset will be selected.

As I'm using a database with nearly 60.000 Datasets searching for a part-string is really killing the performance.

Setting an index at all 4 columns will make the size of the SQLite database much too huge. So I didn't use an index at all.

My Select Statement looks like this:

NSString *sql = [NSString stringWithFormat:@"SELECT * FROM scode WHERE a LIKE '%@%@%@' OR c LIKE '%@%@%@' OR d LIKE '%@%@%@'", wildcard, searchBar.text, wildcard, wildcard, searchBar.text, wildcard, wildcard, searchBar.text, wildcard, wildcard, searchBar.text, wildcard];

Is there any good way to enhance the performance of searching for a part-string in all columns?

Thank you and kind regards,

Daniel

解决方案

You're after Full Text Searching, which SQLite doesn't natively support. I don't have any experience with 3rd party support, but based on results there are a few options.

这篇关于更好的 SQLite Select 语句性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！