I'm running into a problem with partial indexes; apparently the query optimizer
isn't smart enough.
I currently have indexes of the form
CREATE INDEX Index1 ON Table (expr1)
CREATE INDEX Index2 ON Table (expr2)
where expr1 and expr2 are expressions involving table columns.
The problematic queries are of the form
SELECT * FROM Table WHERE (expr1 > val1 OR expr2 > val2) AND expr3
Such a query correctly uses the above indexes — the EXPLAIN command shows it's
using a multi-index OR combining two 'search table using index' loops.
If, however, I try to make the indexes smaller by changing them to
CREATE INDEX Index1 ON Table (expr1) WHERE expr3
CREATE INDEX Index2 ON Table (expr2) WHERE expr3
the query stops using the indexes effectively. It's reduced to doing 'scan
table using index', i.e. O(n).
It looks like what happens is that the optimizer doesn't associate the "AND
expr3" clause with the "expr1" and "expr2" comparisons. In other words, it
doesn't realize that (A OR B) AND C is equivalent to (A AND C) OR (B AND C).
If this were a hand-written SELECT statement it would be easy to work around
this, but it's not. It's the output of a query translator that generates SQL,
and it can generate arbitrary queries with arbitrary combinations of operators.
I know the SQLite optimizer isn't a Mathematica-grade symbolic logic analyzer!
But I'm wondering if in this case there's a way around this limitation?
—Jens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
