KEY id_2 (id)
-
Today I realised that I've copy&paste-ed about 40 times in my life the fragment of SQL code, found years ago in some tutorial, without understanding - I know, that's WTF :(
But today I found the MySQL documentation and decided to understand the mistical:
PRIMARY KEY (ID),
UNIQUE KEY ID (ID),
KEY ID_2 (ID)mantra, that repeated at the end of every table creation. So this is creating three indexes for the very same column ID, moreover one of these indexes, when listed using 'show indexes from..' lacks the information about the uniqueness of ID. I'm not sure how DB engine handles that, but it would be a nightmare if it creates three real copies of the index. And here is why:
Apparently I'm not the only one person who blindly copy&paste-ed this mantra - over 11 000 hits in google!
On some of those pages people suggest that the meaning of those lines is "ID should be primary key, unique and...well we don't know what the last line does"...
I've found some webpages that used something like KEY ID_2 (ID, OtherColumn), which makes more sense, but still I don't understand why they use PRIMARY, and UNIQUE, if PRIMARY implyies UNIQUE...
Do you know the origin of this mantra?
Could you tell me how much storage it takes in comparison to simply defining ID with PRIMARY KEY attribiute?
-
Apart from the mystery last line I shouldn't worry about it (except for the fact you don't know what you are really doing which, as you pointed out, is kind of a stupid way of going about things).
Primary key implies unique - I doubt it creates more than one index.
-
If it doesn't create more than one index, then I don't know how to explain to myself, why
alter table x drop index id
took about 60 seconds, but left the primary index intact.
-
(thread moved to Coding related questions and topics, since it's not at all Oracle related)
