Jens Alfke wrote: > the WHERE clause in a CREATE INDEX statement explicitly disallows > function calls, so I can’t constrain the index to only the rows that > contain the JSON property. Is this limitation something that might be > lifted soon (now that functions can be identified as ‘pure’), or is it > somehow a direct consequence of the way partial indexes work?
Changing the function in any way (including not registering the function) would essentially corrupt the index. > CREATE INDEX profs ON dataset (json_extract(doc, ‘$.professor’)) WHERE > json_extract(doc, ‘$.professor’) IS NOT NULL > > (Now, maybe I’m prematurely optimizing, and in reality all those null > values don’t have much overhead If only 10% of your rows have a professor, the first 90% of the index entries will be NULL. This is probably not a performance problem when searching in a B-tree (you can test this with a table with 'real' columns). Whether the storage overhead matters is something you have to decide yourself. Regards, Clemens _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
