List:General Discussion« Previous MessageNext Message »
From:C.R. Vegelin Date:October 6 2005 1:08am
Subject:Re: How to avoid redundancy between PK and indices ?
View as plain text  
Hi Hank,
You are quite right.
I need separate non-unique indices on a, b, c, d, e and f to avoid table 
scans.
And when each combi must be unique I need a Primary Key (a,b,c,d,e,f).
And only Key a (a) seems to be redundant with the primary key ...
Suppose there would be a PK (a,b,c,d,e,f) defined, without a "separate" PK 
index.
And let's assume some rows like:
> columns:    a   b   c   d   e   f
> row1 has:  1  1   1   1   1   1
> row2 has:  1  1   1   1   1   2
> row3 has:  1  1   1   1   1   3
> etc.
Then checking on unique PK could be done by MySQL internally with:
> Select Count(*) From myTable Where a=1 And b=1 And c=1 And d=1 And e=1 And 
> f=1;
to avoid a duplicate primary key for row1, by using / joining the separate 
"index tables".
With this Select query, MySQL could / should make use of the 6 existing 
separate indices.
Uniqueness can be fully guaranteed with these 6 non-unique indices in this 
case.
In other words, a separate PK index is fully redundant in this case, right ?
In addition, it would save space without the longer concatenate key of 
a+b+c+d+e+f.
Thanks, Cor

----- Original Message ----- 
From: "Hank" <heskin@stripped>
To: "C.R. Vegelin" <vegelin@stripped>
Cc: <mysql@stripped>
Sent: Wednesday, October 05, 2005 5:57 PM
Subject: Re: How to avoid redundancy between PK and indices ?


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

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1



Thread
How to avoid redundancy between PK and indices ?C.R. Vegelin4 Oct
  • Re: How to avoid redundancy between PK and indices ?Alec.Cawley4 Oct
    • Re: How to avoid redundancy between PK and indices ?C.R. Vegelin4 Oct
      • Re: How to avoid redundancy between PK and indices ?Alec.Cawley4 Oct
  • Re: How to avoid redundancy between PK and indices ?Hank5 Oct
  • Re: How to avoid redundancy between PK and indices ?C.R. Vegelin6 Oct
    • Re: How to avoid redundancy between PK and indices ?Hank6 Oct