Try adding another index:
CREATE INDEX a_f1_f2 ON a (f1, f2);
Does that help?
Aaron Burghardt
On Jul 11, 2005, at 6:07 AM, Ben Clewett wrote:
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.