I am porting an application (American Football Results Picking Competition) over from a Postgres databaseo to SQLite which involves some fairly intense queries. I am doing this partially to do some performance comparisons although I have some other reasons too.
I just want to make sure that I am setting up the indexes to some of the tables correctly. Here is an example of a representative type of table CREATE TABLE div_winner_pick ( cid integer NOT NULL REFERENCES competition(cid) ON UPDATE CASCADE ON DELETE CASCADE, -- Competition ID confid character(3) NOT NULL REFERENCES conference(confid) ON UPDATE CASCADE ON DELETE CASCADE, --Conference ID divid character(1) NOT NULL REFERENCES division(divid) ON UPDATE CASCADE ON DELETE CASCADE, --Division ID uid integer NOT NULL REFERENCES participant(uid) ON UPDATE CASCADE ON DELETE CASCADE, --User ID tid character(3) NOT NULL REFERENCES team(tid) ON UPDATE CASCADE ON DELETE CASCADE, --Team who will win division submit_time bigint DEFAULT (strftime('%s','now')) NOT NULL, --Time of submission PRIMARY KEY (cid,confid,divid,uid) ); where the Primary key references several columns For this particular table - in my Postgres definition I created the following two indexes CREATE INDEX div_win_pick_uid_idx ON div_winner_pick (uid); CREATE INDEX div_win_pick_cid_idx ON div_winner_pick (cid); i.e. Two of the 4 fields that make up the primary key. and I was anticipating doing the same - or something similar - I am not yet convinced I don't need to do CREATE INDEX div_win_pick_uid_cid_idx ON div_winner_pick (uid,cid); However, I came across the following text on the SQLite Web Site as part of the explanation of the CREATE TABLE command "INTEGER PRIMARY KEY columns aside, both UNIQUE and PRIMARY KEY constraints are implemented by creating an index in the database (in the same way as a "CREATE UNIQUE INDEX" statement would). Such an index is used like any other index in the database to optimize queries. As a result, there often no advantage (but significant overhead) in creating an index on a set of columns that are already collectively subject to a UNIQUE or PRIMARY KEY constraint." I just wanted to check that lack of advantage (and overhead) applies purely to an index across all columns of the primary key and that if I need the index across a lesser number of columns (because I am querying for all records that match where I can define the values "cid" and "uid" in the example above) it is still and advantage to create it separately. -- Alan Chandler http://www.chandlerfamily.org.uk _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users