Welcome to WebmasterWorld Guest from 107.22.109.65

Forum Moderators: open

Message Too Old, No Replies

keys and indexes question

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

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:July 24, 2002
posts:1126
votes: 0


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!
8:18 pm on Jan 21, 2011 (gmt 0)

Administrator

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

joined:July 31, 2003
posts:12547
votes: 2


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).
7:21 pm on Jan 23, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member Top Contributors Of The Month

joined:July 24, 2002
posts:1126
votes: 0


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

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

Administrator

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

joined:July 31, 2003
posts:12547
votes: 2


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.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members