sqlite Query optimisation

Question

The query

SELECT * FROM Table WHERE Path LIKE 'geo-Africa-Egypt-%'

can be optimized as:

SELECT * FROM Table WHERE Path >= 'geo-Africa-Egypt-' AND Path < 'geo-Africa-Egypt-zzz'

But how can be this done:

select * from foodDb where Food LIKE '%apples%";

how this can be optimized?

Answer 1

It would surprise me if it was faster, but you could try GLOB instead of LIKE and compare;

SELECT * FROM foodDb WHERE Food GLOB '*apples*';

Answer 2

One option is redundant data. If you're querying a lot for some fixed set of strings occuring in the middle of some column, add another column that contains the information whether a particular string can be found in the other column.

Another option, for arbitrary but still tokenizable strings is to create a dictionary table where you have the tokens (e.g. apples) and foreign key references to the actual table where the token occurs.

In general, sqlite is by design not very good at full text searches.