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

Thanks for your comment. Well, we disagree on a few points.
Suppose I have a table with columns CountryID, CompanyID, SectorID and 
ProductID.
And let's say that all these columns are NOT NULL, but indexed as 
non-unique.
I need to select on specific countries, specific products etc.
So I need 4 separate indices, where CountryId may occur more than once in 
the CountryId index,
CompanyID may occur more than once in the CompanyID index etc.
But if these 4 columns together are defined as Primary Key, then each combi 
of  CountryID, CompanyID, SectorID and ProductID is unique.
In my point of view this can only be realized with a separate PK index, 
leading to redundancy in the indices.

My theory is backed by what I read in the manual ... if I read it right ...
If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created 
in a separate batch.
ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique indexes 
for a MyISAM table.
ALTER TABLE ... ENABLE KEYS then should be used to re-create missing 
indexes.
These 2 features can only be realized if MySQL uses separate indices for 
non-uniques and for PK's.
Don't you think ?
Regards, Cor


From: <Alec.Cawley@stripped>
To: <vegelin@stripped>
Subject: Re: How to avoid redundancy between PK and indices ?


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