homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Best SQL indexes to use in MySQL?
JAB Creations

WebmasterWorld Senior Member jab_creations us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4393184 posted 10:14 pm on Dec 1, 2011 (gmt 0)

I've always set the first column (the "id" equivalent that I use for JOIN queries) to auto_increment and set it as the primary key. Reading MySQL's documentation there are several types of indexes to use: PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT. In general is primary key the way to go and if not what contexts apply for each type please?

- John



WebmasterWorld Senior Member 5+ Year Member

Msg#: 4393184 posted 4:02 pm on Dec 2, 2011 (gmt 0)

The keys are implementation specific. You use a primary key if you need to have a unique identifier for each row in a table no matter what. PRIMARY is like UNIQUE but the later allows NULL values to exist in multiple rows.

Using a primary key when is not needed increases the table size and can bring various side effects subject to the implementation.

For example let's say I have a many to many relationship table between products and categories. A primary key won't help me here because:
1. I may access a category to retrieve its products
2. I may access a product to retrieve what categories it belongs to.
Neither the product nor the category can be a primary key. The combination of the two can be a primary key but then the queries I execute have to follow a specific order and I want to utilize the key. Products first then Categories or the opposite. But I can only have one primary key. So you can see the disadvantage.

So instead I will use 2 INDEX keys in this case and deploy code to ensure there are no duplicates when I insert new rows.

For FULLTEXT type, you want it for searches right? the thing is it only applies to certain db engine types. It won't work with InnoDB and you have to use MyISAM which is faster on the one hand but also crashes faster on the other.

As a side note, there were discussions providing the FULLTEXT index since 05 and I read the MySQL 5.6 version has support for FULLTEXT/InnoDB haven't got a chance to try it though. I cannot find the 5.6 community edition yet.

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