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