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.
Alec
"C.R. Vegelin" <vegelin@stripped>
04/10/2005 15:10
To
<mysql@stripped>
cc
Subject
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
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1
