On Oct 14, 2010, at 10:43 PM, Alan Chandler wrote:
> 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.
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.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
