I'm creating some indices, and want to know if it's going to help or hurt me (or be totally irrelevant because of optimization) to include the primary key at the end of the index. All of my tables (in question) have an INTEGER PRIMARY KEY AUTOINCREMENT field, and I have several textual descriptors for the tables in question. I want the fastest lookups (of course) of the PRIMARY KEY value given the text descriptor(s).
It is my understanding that each index is a btree ordered starting with whatever columns you're indexing, and the rowid for subsequent lookups of fields not included in the index. The rowid in my case is, of course, the same as the PRIMARY KEY that I'm after, so I believe this will skip a lookup in the actual table. Enough words.. concrete example: CREATE TABLE t (id INTEGER PRIMARY KEY AUTOINCREMENT, a TEXT, b TEXT, c TEXT); CREATE INDEX i1 ON t (a); -- is this sufficient? CREATE INDEX i2 ON t (a, id); -- or is this necessary to avoid going back to the original table? CREATE INDEX i3 ON t (b); CREATE INDEX i4 ON t (b, id); CREATE INDEX i5 ON t (c); CREATE INDEX i6 ON t (c, id); Also, a, b, and c may be constrained independently or together. So, should I also create a composite index including them all? In every possible ordering? (a,b,c) (a,c,b) (b,a,c) (b,c,a) (c,a,b) (c,b,a) I guess that would cover all bases, but seems like overkill. I think the answer to this question is just let a,b and c ride independently as in the earlier indices, and I'll have constrained alot, though not as fully as possible. Thanks in advance for any insight, Trey
