List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:October 4 2005 12:52pm
Subject:Re: How to avoid redundancy between PK and indices ?
View as plain text  
"C.R. Vegelin" <vegelin@stripped> wrote on 04/10/2005 12:52:01:

> 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.

I do not think this is true. If you specify an index, be it primary or 
not, there is only one index. As you describe it, none of the separate 
columns is a candidate for a primary key, visible or otherwise, because 
none of them is of itself unique.


> And apart from uniqueness, this primary key does not have any added 
> value for programming purposes. Right ?

I believe that if you have InnoDB tables, searching by the primary key is 
likely to be significantly faster than searching by secondary keys.

> My question: does MySQL allow some kind of virtual primary key, 
> where uniqueness is enforced by MySQL by checking its member indices ?

In MyISAM tables, the Primary key, or UNIQUE keys have no other function 
than this. In structure, a primary key is no different to any other key.

> 
> 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.

I think you are wrong in your presumption that there are individual 
indexes. Consider a telephone directory: this may be regarded as indexed 
on FamilyName, GivenName. There is only one index even though it is over 
two fields (the order in the telephone directory). You would only need 
another index if you wanted to search over GivenName,FamilyName. This 
would then require an extra index, which would have to be put in the back.

        Alec Cawley



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