On Thu, Dec 22, 2016 at 1:21 PM, Jens Alfke <j...@mooseyard.com> wrote:
>
> > On Dec 21, 2016, at 7:47 PM, Gelin Yan <dynami...@gmail.com> wrote:
> >
> > the json_extract part didn't work.
> >
> > Query plan indicated "search table t_expr using index pid>? and pid<?"
>
> That’s going to happen with any query like that; it doesn’t have anything
> to do with JSON or an index on an expression.
>
> The index is sorted by `pid`, so the query can use the index to scan only
> the entries with `pid` in the right range. So far so good.
>
> But the `$.index.id` values aren’t in order within the index! They’re
> only sorted _among entries with the same `pid`_. So there’s no possible
> shortcut using the secondary key; the query has to scan every single item
> within that `pid` range.
>
> What you can do is swap the priorities in the index:
> create index if not exists t_expr_idx1 on
> t_expr(json_extract(payload,'$.index.id’)), pid)
>
> Now the query can scan the index starting at the desired `index.id` and
> minimum `pid`, and then scan forwards to the maximum `pid`.
>
> The moral of the story is that, if your query has an equality test and an
> comparison or range test, the primary key of the index should be the
> column/expression being tested for equality.
>
> —Jens
>
>
Hi Jens
I change the index priority as you suggested and it works quite well.
Thank you very much for your valuable advice.
Regards
gelin yan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
