CODEWITHSUNDEEP
sqlitequery-optimization
Rakesh
Rakesh

Reputation: 15047

sqlite Query optimisation

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?

Upvotes: 1

Views: 248

Answers (2)

laalto
laalto

Reputation: 152797

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.

Upvotes: 1

Kim Gr&#228;sman
Kim Gr&#228;sman

Reputation: 7586

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*';

Upvotes: 0

Related Questions