Better performance for SQLite Select Statement

Question

Hi Guys,

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

Answer 1

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.

Answer 2

You answered your own question: Do the index on all four columns. And measure the size difference. Considering the storage capacity of the iPhone, you're probably out of balance trying to reduce storage.

The rule of thumb with SQLite performance is not to doa query that isn't indexed.

You can see what SQLite is actually doing by creating your database on the Mac using the same schema and EXPLAIN QUERY PLAN. (There's also EXPLAIN, which is more detailed but less obvious.)

Answer 3

You can create a separate table, with two columns: a pattern string and a key value (which is used to refer to your data tables). Lets call this table "search_index".

Then, on any change to your data table entries, you update the "search_index" table:

1. remove rows with keys of changed data table rows
2. for each column in data table, use the first X characters of the data, and add them to search_index with the key

You can work out the details yourself, but in this way, you just build your own (partial) search index.

When querying, you can use up to X characters to search in the search_index table alone. If the user types more than X characters you at least have a limited set of data table rows to search in. So you can search those 60k rows easily.

Find a good value for X to balance storage requirements and usability and performance.

EDIT: Looks like you do not want to search only the beginning of the words? Well, then you should not just use the "first X characters", but you should split the data into single words, and use the full words in search_index. Though in practice you will still have around a fourth of the index storage requirements compared to giving all columns an index. So, its still a good thing to build your own "search_index".