> 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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
