I'm using a SQLite database with a single-table schema. The table includes a column for JSON data. I have some shared logic that queries for rows where various JSON fields match some value. So I have a generic query like the following:
SELECT thing_data FROM thing WHERE json_extract(thing_data, "$key1") = "$val1"; I've added an index on some specific JSON fields like the following: CREATE INDEX thing_name ON thing(json_extract(thing_data, "$.name")); I've found that query plans for select statements like the one above do *not* use the index. But, if I change the query to hard-code the JSON field path, the query planner uses the index: SELECT thing_data FROM thing WHERE json_extract(thing_data, "$.name") = "$val1"; There is a relevant note in the sqlite3_prepare_v2 docs: If the specific value bound to host parameter in the WHERE clause might > influence the choice of query plan for a statement, then the statement will > be automatically recompiled, as if there had been a schema change, on the > first sqlite3_step() call following any change to the bindings of that > parameter. The specific value of WHERE-clause parameter might influence the > choice of query plan if the parameter is the left-hand side of a LIKE or > GLOB operator or if the parameter is compared to an indexed column and the > SQLITE_ENABLE_STAT3 compile-time option is enabled. So my question is: if I bind a value to the $key1 host parameter that makes the json_extract expression match one used in an index-on-expression, should the query planner then make use of the index-on-expression after the statement is recompiled? Or, is the index-on-expression only used when the original SQL text contains the exact expression, before any parameter binding? Thanks! Kris _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
