Simon Slavin <slav...@bigfraud.org> wrote: > 1) My index does not specifically involve ordering the entries in 'id' order. > But entries in the table are always entered > chronologically. Am I right that SQLite implicitly adds the rowid into every > index to keep entries unique ?
Not so much to keep them unique, but to be able to refer back to the row this index entry came from (an index would be pretty useless otherwise). But yes, an index always implicitly has rowid as the last column. > In other words, if I do > > SELECT id FROM changes WHERE theTable='customers' AND theRowID=123 ORDER BY > id DESC LIMIT 1 > > will SQLite figure out that it can do this directly from the index ? I believe so, but you can confirm this with EXPLAIN QUERY PLAN. > 2) Can SQLite optimize searches across 'OR' ? In other words if I do > > SELECT id FROM changes WHERE theTable='customers' AND (theRowID=123 OR > theRowID='*') ORDER BY id DESC LIMIT 1 > > will it use the index once for 123 and again for '*' ? I used to advise rewriting such a query with UNION ALL, because once upon a time, SQLite reverted to a table scan as soon as it saw an OR clause. But at one point, Mr. Hipp chimed in and said this was no longer true, that recent versions of SQLite could perform such a transformation automatically. I don't remember which version is the first that does it, but the conversation took place at least a year ago. Confirm with EXPLAIN QUERY PLAN how your SQLite version executes this query. Personally, I'd write the check as theRowID IN (123, '*'). Not that it should make any difference, apart from making the statement a bit more compact and perhaps easier to read. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
