Doug Nebeker wrote: > CREATE UNIQUE INDEX Ind_DevProps_CompIDPropID on DevProps (CompID, PropID); > > When I check the query plan for this statement: > > SELECT CompID FROM DevProps WHERE PropID=33 > > it looks like it will be doing a table scan.
My version of SQLite uses a covering index, but is still doing a full scan. > Why is that? The index contains the values of the indexed columns, and for each index entry, a pointer back to the table record. In your case, it might look like this: CompID PropID rowid 1 11 * 1 22 * 1 33 * 2 22 * 3 33 * 3 44 * 4 11 * 4 22 * Please note that the index is sorted by CompID first, and then by PropID. How would this index be helpful for looking up entries with PropID=33, without doing a full scan? To be useful for lookups, the lookup column(s) must be the leftmost column(s) in the index. Regards, Clemens _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
