enigma1 - 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.