Interesting and thanks for that tip. Is there a performance penalty from structuring the query like that? I take it that there will be.
RBS > 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] > ----------------------------------------------------------------------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
