On Oct 14, 2010, at 11:56 PM, Alan Chandler wrote: > On 14/10/10 17:28, Dan Kennedy wrote: >> >> On Oct 14, 2010, at 10:43 PM, Alan Chandler wrote: >>> CREATE TABLE div_winner_pick ( > ... >>> PRIMARY KEY (cid,confid,divid,uid) >>> ); > > ... >>> >>> CREATE INDEX div_win_pick_uid_cid_idx ON div_winner_pick (uid,cid); > > > ... > >> It should be clearer. Basically the index would be redundant >> if it contains the same columns in the same order as the primary >> key. Or a prefix thereof. i.e. the following indexes would be >> all be redundant (pure overhead for no benefit): >> >> CREATE INDEX x ON div_pick_winner(cid); >> CREATE INDEX x ON div_pick_winner(cid,confid); >> CREATE INDEX x ON div_pick_winner(cid,confid,divid); >> CREATE INDEX x ON div_pick_winner(cid,confid,divid,uid); >> >> Your index is not redundant though. > > This is interesting - what if I changed the primary key to be > > PRIMARY KEY (uid,cid,confid,divid) > > Is that an optimisation that is useful to make?
Quite possibly. If you change the primary key to the above then you can leave out the (uid, cid) index. All queries that would have used the (uid, cid) index will use the automatic index created by the PRIMARY KEY instead. One less index means a smaller database file and less work for SQLite to do when you insert/update/delete a row. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
