*** 179,182 **** --- 179,225 ---- use a transaction then SQlite will open and close the database 100 times. Using transactions improves speed. Use them.</p> + <H2>How to get SQLite to use multiple indexes</H2> + + <p>SQLite can use multiple indexes on a query. You just have to tell it to explicitly by restructuring your SQL.</p> + + <p>As an example, consider this query:</p> + + <pre> + SELECT * FROM table1 WHERE a=5 AND b=11; + </pre> + + <p>Suppose there are two indices:</p> + + <pre> + CREATE INDEX index1 ON table1(a); + CREATE INDEX index2 ON table1(b); + </pre> + + <p>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.)</p> + + <p>If you want to use both indices, rewrite the query this + way:</p> + + <pre> + SELECT * FROM table1 WHERE rowid IN + (SELECT rowid FROM table1 WHERE a=5 + INTERSECT SELECT rowid FROM table1 WHERE b=11); + </pre> + + <p>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.</p> + + </html>
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|