Wout Mertens wrote:
> I have a table with a bunch of data (in json). I want to search on several
> values, each one is indexed. However, if I search for COND1 AND COND2, the
> query plan is simply
> SEARCH TABLE data USING INDEX cond1Index (cond1=?)
> Is this normal?
Yes. A query can use only a single index per table.
> Should I be creating indexes differently to cover two dimensions at once?
Create an index on both colums:
CREATE INDEX cond1and2Index ON data(cond1, cond2);
If you are not using equality comparisons on all but the last columns in
the index, you might need an expression index. (With JSON, I guess you
are already doing this.)
> Perhaps with r*tree?
That would be useful mainly for multidimensional, numeric range queries
(e.g., "xColumn BETWEEN ? AND ? AND yColumn BETWEEN ? AND ?").
sqlite-users mailing list