Dear SQLite,
I have found a possible area where the SQL logic of SQLite is not as
good as other DBMS. Like MySQL or PostgreSQL. It's a simple thing, so
I am showing it as a candidate for work. (If I could submit a patch I
would :)
If you create a table with two indexes:
CREATE TABLE a (
f0 INTEGER PRIMARY KEY,
f1 INTEGER,
f2 INTEGER
);
CREATE INDEX a_f1 ON a (f1);
CREATE INDEX a_f2 ON a (f2);
Now execute a query using both the indexes:
SELECT * FROM a WHERE f1 = 1 OR f2 = 1;
This query will not use the indexes. The query will increment through
every row in the table. This is obviously very slow.
As I mensioned, MySQL and PostgreSQL will use the indexes here, and
therefore return the result considerably faster.
I can use the 'UNION' to get the result I am after, so it's not a show
stopper.
I hope this is of interest to somebody.
Regards,
Ben Clewett.
