Re: How to avoid redundancy between PK and indices ?

[Hank]

It depends.. if this is your create table statement:

CREATE TABLE foo (
  a smallint NOT NULL,
  b smallint NOT NULL,
  c smallint NOT NULL,
  d smallint NOT NULL,
  e smallint NOT NULL,
  f smallint NOT NULL,
  PRIMARY KEY  (a,b,c,d,e,f)
);

Then only one unique index is being created on the concatenate key of
a+b+c+d+e+f.  Queries on any fields other than A will cause a full
table scan.

On the other hand, if your create table is:

CREATE TABLE foo (
  a smallint NOT NULL,
  b smallint NOT NULL,
  c smallint NOT NULL,
  d smallint NOT NULL,
  e smallint NOT NULL,
  f smallint NOT NULL,
  PRIMARY KEY  (a,b,c,d,e,f),
  KEY a (a),
  KEY b (b),
  KEY c (c),
  KEY d (d),
  KEY e (e),
  KEY f (f)
);

This will create the primary key, plus six additional indexes, each of
which is queryable. But in this case, the "KEY a (a)" non-unique index
is redundent with the primary key, so to do what you want - a unique
index on a+b+c+d+e+f PLUS the ability to independtly search the  b c d
e and f fields, here is the create table you'll need to use:

CREATE TABLE foo (
  a smallint NOT NULL,
  b smallint NOT NULL,
  c smallint NOT NULL,
  d smallint NOT NULL,
  e smallint NOT NULL,
  f smallint NOT NULL,
  PRIMARY KEY  (a,b,c,d,e,f),
  KEY b (b),
  KEY c (c),
  KEY d (d),
  KEY e (e),
  KEY f (f)
);


--

-Hank