List:General Discussion« Previous MessageNext Message »
From:C.R. Vegelin Date:October 4 2005 11:52am
Subject:How to avoid redundancy between PK and indices ?
View as plain text  
Hi List,

Is anyone familiar with optimizing indices, including primary key ?
I do have a large myisam table with 6 non-unique key fields, lets say named A, B, C, D, E
and F.
Each of these columns may have NOT NULL values from 0 to 999, and are defined as SmallInt.
Requirement: each row must have a unique combination of these 6 key fields (all Btree) !

To force uniqueness on this table, I can define a PRIMARY KEY (A, B, C, D, E, F);
But I suppose that MySQL makes a separate (physical) index for the primary key, besides
the 6 member indices.
And apart from uniqueness, this primary key does not have any added value for programming
purposes. Right ?
My question: does MySQL alllow some kind of virtual primary key, where uniqueness is
enforced by MySQL by checking its member indices ?

At this moment my table has more than 13 million rows (about 1100 MB Data_Length).
And the Index_Length is about 500 MB, for the 6 indices and the primary key, consisting of
these 6 indices.
In this case a virtual primary key could save maybe 200 MB in stead of a real primary key
index and could speed up the updating processes.

I like to hear from you.
Thanks, Cor

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