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.

Accepted Answer

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
```

sqlite index on strings columns

Solution

Problem