Welcome to WebmasterWorld Guest from 54.221.119.45

Forum Moderators: open

Message Too Old, No Replies

Best SQL indexes to use in MySQL?

     

JAB Creations

10:14 pm on Dec 1, 2011 (gmt 0)

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



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

enigma1

4:02 pm on Dec 2, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month