Mysql Index

[designanddev]

can some define the differences between the 4 indexes please...

 

Primary

Unique

Index

Fulltext

 

Kind regards

[Barand]

Primary - every table should have one of these and it is a unique identifier for each row in the table. It usually, but not necessarily, an auto_incrementing identifier.

 

Unique - similar to a primary. You may have more than one candidate identifier for the primary, eg Employee table has employee number as the primary key but you want to constrain the data so that their national insurance numbers are also unique (and username for company intranet) so define these as unique to prevent insertion of duplicates.

 

Index - values do not have to be unique but, like a book, they enable fast lookup of records.

 

Fulltext - gives powerful word searching capabilities on fields eg find all articles (title and body) that contain macintosh or apple but not raincoat or newton. Fulltext searches can also give a relevancy score depending on how many of the search terms are contained allowing you to get the most relevant first