>> Furthermore, the "id" is unique so if there is a constraint on the "id" the >> query planner will always use that constraint to look up the rows in the >> table directly, rather than going through an index, since doing so will be >> about twice as fast as using an index.
>Could you explain that last bit? I always thought UNIQUE was implemented >under the hood with a regular index. How would simply knowing something >is unique make it so much easier to find the needle in the haystack >without an index? Tables in SQLite are indexes. That is, tables are implemented as b-tree structures where the rowid (id -- integer primary key) is the unique key to the b-tree and thus to the entire row. Or you could look at it that a Table is a covering index of all the columns in a table that does not exist. (Not exactly -- as a Table has the row data as the payload, whereas an index includes the rowid (id) as part of the key and is payloadless). This applies *only* to the rowid (integer primary key). Other indexes (as in CREATE INDEX) are always unique since the key always contains the rowid as the final (unspoken) component. A "unique" index must be unique without considering the (unspoken) rowid component of the key. So a manual index that is created with the rowid as the final component is always unique (whether you specify it or not) and the rowid is placed in the key twice (once spoken, once unspoken). --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
