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
