I have a table with an indexed json field, and I want to know the maximum
value of that field.
create table events(id integer primary key, json JSON);
create index t on events(json_extract(json, '$.ts));
If I do
select max(json_extract(json, '$.ts')) from events;
it does a table scan with the index, as far as I can decode the query plan
it's doing a max() on every row. It takes 150ms on my data.
If instead I do
select json_extract(json, '$.ts') from events order by
json_extract(json, '$.ts' ) desc limit 1;
I get the same result in 6ms.
The same queries on a real field, like `id`, always return in 0-2ms.
Is this an optimization opportunity for calculated indexes?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
