On Aug 17, 2009, at 11:05 AM, John Machin wrote: > On 17/08/2009 11:41 AM, Shane Harrelson wrote: >> INDEXED BY doesn't allow you to specify which index to use. It >> just causes >> the query to fail if SQLite thinks it should use an index different >> then the >> one specified by the INDEXED BY clause. > > Oh. The docs say "If index-name does not exist or cannot be used for > the > query, then the preparation of the SQL statement fails." Please > consider > submitting a docs bug report (with evidence).
The two statements are not incompatible. What happens in the code is that for a table with an "INDEXED BY" clause attached, SQLite does not consider any other indexes or a linear scan when planning a query. SQLite will not do a full scan of an index (unless this helps with an ORDER BY clause). If this means no valid plan is found, query compilation fails. So if you try this: CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); SELECT * FROM t1 INDEXED BY i1; Then the SELECT statement fails (to prepare) as SQLite cannot find a plan where it can use index i1 (advantageously). But if you do this: SELECT * FROM t1 INDEXED BY i1 ORDER BY a; Then this will prepare and run fine. The following will also work: CREATE INDEX i2 ON t1(b); SELECT * FROM t1 INDEXED BY t1 WHERE b=10 ORDER BY a; In this case, depending on the stats collected by any ANALYZE command, SQLite will normally use index i2 to optimize the b=10 constraint. But with the INDEXED BY, it uses index i1 to optimize the ORDER BY instead. Presumably the user knows something about the contents of table t1 that has allowed her to conclude that using index i1 will be more efficient in this case. The INDEXED BY feature was introduced to address concerns that SQLite might suddenly start using a different plan for a query in the field than it did in the office during testing. Either because somebody ran ANALYZE, or because the SQLite version was upgraded. In this situation, some users consider it better to throw an exception than to run the query with a different, possibly slower, plan. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
