keys and indexes question

[jamie]

hi,

i recently discovered one of our tables looking like this:


CREATE TABLE IF NOT EXISTS `tags` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`section_ID` int(11) NOT NULL,
`tag` varchar(20) NOT NULL,
PRIMARY KEY (`ID`),
KEY `ID` (`section_ID`,`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

note the KEY 'ID' (`section_ID`,`tag`).

i don't remember creating it like this, normally i would create it:

CREATE TABLE IF NOT EXISTS `tags` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`section_ID` int(11) NOT NULL,
`tag` varchar(20) NOT NULL,
PRIMARY KEY (`ID`),
KEY `section_ID` (`section_ID`),
KEY `tag` (`tag`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

using distinct KEY indexes.

can anyone explain the difference please?

many thanks!

[coopster]

The first is a multiple-column index, compound index, compound key ... many different names/aliases. MySQL refers to it as a multiple-column index. The information in your table would be sorted by the value in

section_ID

and then by

tag

.

The second is a single-column index, sorted by that column specifically with no specific sort on any secondary column(s).

[jamie]

thanks coopster - still baffled as to why it was setup like that (i am the only one with access to the db).

cheers

[coopster]

Well, what is really odd to me is the specific note to which you draw attention. Typically the name of the INDEX/KEY is the first column name in the INDEX/KEY definition, as described in the MySQL documentation for CREATE TABLE Syntax [dev.mysql.com]:

In MySQL, the name of a PRIMARY KEY is PRIMARY. For other indexes, if you do not assign a name, the index is assigned the same name as the first indexed column, with an optional suffix (_2, _3, ...) to make it unique. You can see index names for a table using SHOW INDEX FROM tbl_name. See Section 12.4.5.23, "SHOW INDEX Syntax".

However, in your case it is not. It's almost like the table had a different definition at one point in time and has been altered since.