Welcome to WebmasterWorld Guest from 54.147.44.93

Forum Moderators: open

Message Too Old, No Replies

keys and indexes question

     

jamie

8:44 pm on Jan 20, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

8:18 pm on Jan 21, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

7:21 pm on Jan 23, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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

cheers

coopster

3:04 pm on Jan 24, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month