Martin Pelletier <[EMAIL PROTECTED]> wrote:
> This is news to me. Why can't SQlite use more than one index?
>
It can. You just have to tell it to explicitly by restructuring
your SQL.
As an example, consider this query:
SELECT * FROM table1 WHERE a=5 AND b=11;
Suppose there are two indices:
CREATE INDEX index1 ON table1(a);
CREATE INDEX index2 ON table1(b);
As written, SQLite will only use one of these two indices
to perform the query. The choice is arbitrary (unless you
have run ANALYZE and SQLite has some information to help it
pick the "best" index.)
If you want to use both indices, rewrite the query this
way:
SELECT * FROM table1 WHERE rowid IN
(SELECT rowid FROM table1 WHERE a=5
INTERSECT SELECT rowid FROM table1 WHERE b=11);
The optimizer in PostgreSQL will make this change for you
automatically and will use a bitmap to implement the IN
operator and the INTERSECT. With SQLite, though, you have to
type in the expanded version yourself. And because rowids
in SQLite are user visible and changeable and can thus be
diffuse, SQLite is unable to use bitmaps to optimize the
computation. But modulo the bitmap optimization, SQLite gives
you all the capabilities of PostgreSQL, you just have to type
it in yourself rather than letting the optimizer do it for
you.
--
D. Richard Hipp <[EMAIL PROTECTED]>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
