Example Use of MySQL Indexes?

Question

I was reading this question about the difference between these 4: http://stackoverflow.com/questions/707874/differences-between-index-primary-unique-fulltext-mysql

However, it is all very abstract and vague to me after reading it. Maybe it would help me understand it more concretely if I had some examples of when I would use it.

For example, I think for the field user_id, we would use the index UNIQUE, correct?

Answer 1

Here's a brief description of what they are and when to use them:

• INDEX: To speed up searches for values in this column.
• UNIQUE: When you want to constrain the column to only contain unique values. This also adds an index. Example: if you only want each email to be registered once, you can add a unique constraint on the email column.
• PRIMARY KEY: Contains the identity for each row. A primary key also implies a unique index and a "not null" constraint. It is often an auto-increment id, but it could also be a natural key. It is generally a good idea to create a primary key for each table, although it is not required.
• FULL TEXT: This is a special type of index that allows you to perform searches for text strings much faster than LIKE '%foo%'.

Note that I am only considering single column indexes here. It is also possible to have a multi-column index.

Answer 2

if you have a "Person" table with id,name,email and bio information...

• The primary key is the id, maybe it's a number it will allways be unique and you could use it as a reference in other tables (foreign keys)
• the email is unique on each person, so you could add a UNIQUE constraint there
• you might want to search a person over his name constantly so you could add an INDEX there
• and finally a full text search in the bio attribute because it might be useful on a search

primary keys are also UNIQUE

Answer 3

A primary key is not an index, per se --it's a constraint.
The primary key uniquely identifies a row from all the rest - that means the values must be unique. A primary key is typically made of one column, but can be made of more than one - multiple columns are called a composite....

A unique constraint is implemented in MySQL as an index - it guarantees that the same value can not occur more than once in the column(s) it is defined for. A unique constraint/index is redundant on a primary key column, and a primary key could be considered a synonym but with bigger implications. These too support composites...

In MySQL (and SQL Server), there are two types of indexes - clustered and non-clustered. A clustered index is typically associated with the primary key, and automatically created if a primary key is defined in the CREATE TABLE statement. But it doesn't have to be - it's the most important index to a table, so if it's more optimal to associate with different columns then the change should be reviewed. There can only be one clustered index for a table - the rest are non-clustered indexes. The amount of space you have to define indexes depends on the table engine - 1,000 for MyISAM and 767 for InnoDB. Indexes, clustered an non, are used to speed up data retrieval and their use can be triggered by using the columns in SELECT, JOIN, WHERE and ORDER BY clauses. But they also slow down INSERT/UPDATE/DELETE statements because of maintaining that data.

Full Text indexes are explicitly for Full Text Search (FTS) functionality - no other functionality can make use of them. They are only for columns defined with string based data types.

Mind that indexes are not ANSI - the similarities are thankfully relatively consistent. Oracle doesn't distinguish indexes - they're all the same.