Index question

[Matthew Wells]

I have a one to many relationship between two tables. The "one" table has a
5 field PK (total 7 fields, 1400 records) and the "many" table has a 7
field PK (total 15 fields, 1500 records). I followed the general rule of
indexing each field in the PKs in both tables. I was doing a query on both
tables with a "group by" to get the sum of a field in the "many" table. The
query was taking about 5 seconds to run which I thought was horrendous. I
deleted the individual indexes on the PK fields of the "many" table and that
made the query instantaneous. My question is: When creating a PK, does
that automatically index the individual fields? If so, then does my
re-indexing them slow things down?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

[MGFoster]

Matthew Wells wrote:

I have a one to many relationship between two tables. The "one" table has a
5 field PK (total 7 fields, 1400 records) and the "many" table has a 7
field PK (total 15 fields, 1500 records). I followed the general rule of
indexing each field in the PKs in both tables. I was doing a query on both
tables with a "group by" to get the sum of a field in the "many" table. The
query was taking about 5 seconds to run which I thought was horrendous. I
deleted the individual indexes on the PK fields of the "many" table and that
made the query instantaneous. My question is: When creating a PK, does
that automatically index the individual fields? If so, then does my
re-indexing them slow things down?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Creating a multi-column PK creates one index for the combined columns.
E.g. (a simplification):

PK = Col1, Col2

Col1 Col2 PK value
- ------- ------- -------------
AAA BBB AAABBB
CCC DDD CCCDDD

The index is on the PK value column.

Therefore, if you "know" you are going to be using one column of the PK
more often than other PK columns in JOINs or WHERE clauses, that column
should be declared first in the PK declaration. E.g., I want the EmpID
to be the important column in the Sales table PK.

ALTER TABLE Sales
ADD CONSTRAINT PK_Sales PRIMARY KEY (EmpID, SalesDate)

When a query optimization scheme is created it tries to calculate the
best index(es) to use. If there are many indexes refering to the same
column(s) the optimization may be "confused" and not select the best
index(es) to scan during the query run. Simplifying indexes (few, if
any, overlapping indexes) can simplify the optimization scheme
generation and speed up the query.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGxjpoechKqOuFEgEQKfzQCg7YaeOcl1b3nYa8a7gMbSBn WWUGcAoONB
5APh/Tz270D3ItvClSvjaeD8
=sQ32
-----END PGP SIGNATURE-----