On 17/08/2009 2:37 PM, Dan Kennedy wrote: > 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.
AFAICT, NONE of the scenarios you outline below fit Shane's statement "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." For a scenario to match that statement, there must be at least TWO indexes on the table. Only one scenario has two indexes. In that case, whether SQLite "thinks" (looks at the analyze results) or not, you say that the result is that it goes with the index in the INDEXED BY clause ... so your description of that scenario doesn't match Shane's statement at all. On the other hand, all your scenarios match the above doc excerpt -- provided of course that the "use" is interpreted as "use beneficially/advantageously". > > 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 does not consider any other indexes" is NOT compatible with Shane's "SQLite thinks it should use an index different ...". > 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). Yes, indeed, that select statement constitutes a user bug; it is a nonsense, quite irrespective of the presence/absence of i1 or any other index, creating/deleting ANALYZE results, or SQLite version changes. 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. Confusion reigns supreme. Your second last paragraph says (about your last scenario) that it uses index i1 instead of the apparently better index i2 -- no exception throwing. Your last paragraph indicates that in this case an exception would be thrown. The docs are likewise confused -- after the early piece that I quoted (which supports using the INDEXED BY index unless it has vanished or would result in a full scan), we find this "The intent of the INDEXED BY clause is to raise a run-time error if a schema change, such as dropping or creating an index, causes the query plan for a time-sensitive query to change. The INDEXED BY clause is designed to help detect undesirable query plan changes during regression testing." SUMMARY: Three worries with "INDEXED BY i1": (1) i1 doesn't exist => no argument, raise an exception. (2) i1 cannot be used advantageously e.g. there is nothing in a WHERE clause or ORDER BY clause that fits i1 => no argument, raise an exception. (3) i1 exists and could be used (as in better than no index at all) but there is another index i2 that looks even better => option (a) use i1; (b) raise an exception IMHO whichever of (a) and (b) actually happens, some clarification in the docs might be useful. Cheers, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
