sqlite index on strings columns

Question

I have a query. It's taking 1100 ms which I don't think it should.

select * from PageInfo where url = @url OR url like @url2

The url are /sub/id and /sub2/id/thing. I have an index on pageinfo(url). The body of the page is about ~10k and this sample got me 1120ms for 2 rows (<20k). It feels wrong it takes that long. I should be able to get both those pages over the network in less then that time.

What can I do to speed things up? I have an index, i'm not sure what else I can do.

Answer 1

Using like will prevent use of the index.

Compare

sqlite> explain query plan select * from PageInfo where url = @url OR url like @url2;
0|0|0|SCAN TABLE PageInfo (~500000 rows)
sqlite> 

to

sqlite> explain query plan select * from PageInfo where url = @url OR url between @url2 and @url3;
0|0|0|SEARCH TABLE PageInfo USING INDEX pi (url=?) (~10 rows)
0|0|0|SEARCH TABLE PageInfo USING INDEX pi (url>? AND url<?) (~31250 rows)
sqlite> 

You should use between and construct arguments to the query for @url2 and @url3 such as

/sub2/1234/thing?page=0

and

/sub2/1234/thing?page=99999999