Differences between index, primary, unique, fulltext? MySQL

Question

What are the differences between primary, unique, index and fulltext when creating MySQL tables?

How would I use them?

Answer 1

All of these are kinds of indices.

primary: must be unique, is an index, is (likely) the physical index, can be only one per table.

unique: as it says. You can't have more than one row with a tuple of this value. Note that since a unique key can be over more than one column, this doesn't necessarily mean that each individual column in the index is unique, but that each combination of values across these columns is unique.

index: if it's not primary or unique, it doesn't constrain values inserted into the table, but it does allow them to be looked up more efficiently.

fulltext: a more specialized form of indexing that allows full text search. Think of it as (essentially) creating an "index" for each "word" in the specified column.

Answer 2

Differences

• KEY or INDEX refers to a normal non-unique index. Non-distinct values for the index are allowed, so the index may contain rows with identical values in all columns of the index. These indexes don't enforce any structure on your data so they are used only for speeding up queries.
• UNIQUE refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE indexes can be used to enforce structure on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data. Your database system may allow a UNIQUE index on columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (NULL is considered not equal to itself), though this is probably undesirable depending on your application.
• PRIMARY acts exactly like a UNIQUE index, except that it is always named 'PRIMARY', and there may be only one on a table (and there should always be one; though some database systems don't enforce this). A PRIMARY index is intended as a way to uniquely identify any row in the table, so it shouldn't be used on any columns which allow NULL values. Your PRIMARY index should always be on the smallest number of columns that are sufficient to uniquely identify a row. Often, this is just one column containing a unique auto-incremented number, but if there is anything else that can uniquely identify a row, such as "countrycode" in a list of countries, you can use that instead.
• FULLTEXT indexes are different to all of the above, and their behaviour differs more between database systems. Unlike the above three, which are typically b-tree (allowing for selecting, sorting or ranges starting from left most column) or hash (allowing for selection starting from left most column), FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause.

Similarities

• All of these indexes may have more than one columns in them. With the exception of FULLTEXT, the column order is significant: for the index to be useful in a query the query must use columns from the index starting from the left - it can't use just the second, third or fourth part of an index, unless it is also matching constant values in all columns to the left as well. For a FULLTEXT index to be useful to a query, the query must use all columns of the index.