Optimization in SQLite

Question

SQLite

There is a need to speed up the search in the SQLite database in one project on the iPhone.

The database is large, more than a million records. There are several varchar text fields that are being searched. Information in Russian UTF8.

Currently using LIKE + custom lowercase conversion function. The problem is that you can't use an index in this case. As a result, the request is executed for almost a minute.

What can you advise for acceleration?

Answer 1

If the search is conducted by the condition 'XXX%', then the index can be used.
If you need a full text search, then look here

Answer 2

LIKE does not pick up, because there are subtleties. You can read about them here .
In short:
1) a column in a table must be of type TEXT:
foo(bar TEXT) is good, foo (bar) is bad
2) by default: LIKE is case insensitive, and COLLATION (i.e. a way to compare data) is used BINARY, that is regular character-by-character match = case-sensitive.
Therefore, you must either enable the case-sensitive mode for LIKE with the
PRAGMA command case_sensitive_like = 1;
Or specify the required COLLATION type in the index:
CREATE INDEX idx ON foo (name COLLATE NOCASE);
3) LIKE must be written as <COLUMN> LIKE "XXX%"
i.e. on the left is a column, on the right is a string literal that _does not start_ with wildcard.
Checked up a variant with case_sensitive_like = 1; index is used.

Answer 3

The best way is to make SQLite work with the Russian language in a human way. Because the lack of support will still creep out sideways in various other places. But if this is not possible, you can duplicate all the fields that are being searched and store the text in them in lower case. Well, programmatically ensure that the values ​​in them correspond to the original fields.