homepage Welcome to WebmasterWorld Guest from 54.167.75.155
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
keys and indexes question
jamie




msg:4255900
 8:44 pm on Jan 20, 2011 (gmt 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!

 

coopster




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

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




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

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

cheers

coopster




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

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved