List:General Discussion« Previous MessageNext Message »
From:Alec.Cawley Date:October 4 2005 2:23pm
Subject:Re: How to avoid redundancy between PK and indices ?
View as plain text  
I agree that if you want to do the searches you describe, you will need 
extra keys. But MySQL does *not* create these keys automatically - it 
creates the keys you ask for *and no more*. You have asked, correctly, for 
a primary key on ABCDEF. MySQL will create exactly that key and no others. 
This will allow you to search on A, AB, ABC etc. If you want a separate 
search on B, or on D, or on F, *you* must request individual indexes on 
these columns. MySQL does not implicitly create hidden indexes for you; 
there is exactly one index for each PRIMARY KEY/UNIQUE/INDEX. This, of 
course, produces redundancy; but only the redundancy that you request. 
Without these redundant indexes, a search on B will be forced to do a full 
table scan. And you *need* those indexes to do the searches you want. 
Without them, all searches become full table scans. And without a PRIMARY 
KEY index, every insert would have to include a full table scan.


"C.R. Vegelin" <vegelin@stripped> 
04/10/2005 15:10


Re: How to avoid redundancy between PK and indices ?

Hi Alec,

Thanks for your comment. Well, we disagree on a few points.
Suppose I have a table with columns CountryID, CompanyID, SectorID and 
And let's say that all these columns are NOT NULL, but indexed as 
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 
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 
in a separate batch.
ALTER TABLE ... DISABLE KEYS tells MySQL to stop updating non-unique 
for a MyISAM table.
ALTER TABLE ... ENABLE KEYS then should be used to re-create missing 
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 
> 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 
>        Alec Cawley

MySQL General Mailing List
For list archives:
To unsubscribe:

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