Re: Normalization-unique key vs. lookup key?

For the example you have described col A should be the PK and Col B should be have a non-unique index. Creating the index, BA, would only make since if you always access the table by user and never by col A alone and you believe that BA would consume less space than two separate indexes A and B.

If you access the table by sequence you need an index that starts with col A, and because A uniquely identifies each and every row there is not reason to create AB.

If you access by col B then unless you need no other data except the sequence, or have a multi-table join where B is known and A is needed to get to another table, but no other col other than col A is needed from this table then the index BA serves no valid purpose.

General rule - columns that are not referenced in the where clause should not be indexed.

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!