How to use MySQL index columns?

Question

When do you use each MySQL index type?

• PRIMARY - Primary key columns?
• UNIQUE - Foreign keys?
• INDEX - ??

For really large tables, do indexed columns improve performance?

Answer 1

Indexes will improve performance on larger tables. Normally, the primary key has an index based on the key. Usually unique.

It is useful to add indexes to fields that are used to search on a lot too such as Street Name or Surname as again it will improve perfomance. Don't need to be unique.

Foreign Keys and Unique Keys are more for keeping your data integrity in order. So that you cannot have duplicate primary keys and so that your child tables don't have data for a parent that has been deleted.

Answer 2

Primary

The primary key is - as the name suggests - the main key of a table and should be a column which is commonly used to select the rows of this table. The primary key is always a unique key (unique identifier). The primary key is not limited to one column, for example in reference tables (many-to-many) it often makes sense to have a primary key including two or more columns.

Unique

A unique index makes sure your dbms doesn't accept duplicate entries for this column. You ask 'Foreign keys?'. NO! That would not be usefull since foreign keys are per definition prown to be duplicates, (one-to-many, many-to-many).

Index

Additional indexes can be placed on columns which are often used for SELECTS (and JOINS) which is often the case for foreign keys. In many cases SELECT ( and JOIN) queries will be faster, if the foreign keys are indexed.

Note however that - as SquareCog has clarified - Indexes get updated on any modifications to the data, so yes, adding more indexes can lead to degradation in INSERT/UPDATE performance. If indexes didn't get updated, you would get different information depending on whether the optimizer decided to run your query on an index or the raw table -- a highly undesirable situation.

This means, you should carefully assess the usage of indices. One thing is sure on the basis of that: Unused indices have to be avoided, resp. removed!

Answer 3

PRIMARY defines a primary key, yes.

UNIQUE simply defines that the specified field has to be unique, it has nothing to do with foreign keys.

INDEX creates an index for the specified column and, yes, it improves performance for large tables, sorting and finding something in this column can be much faster if you use indexing.

Answer 4

The bigger the table, the bigger is gain from using an index. Do note that indexes makes insert (and probably update) operations slower so make sure you don't index too many fields.

Answer 5

I'm not that familiar with MySQL, however I believe the following to be true across most database servers. An index is a balanced tree which is used to allow the database to scan the table for given data. For example say you have the following table.

CREATE TABLE person (
    id    SERIAL,
    name  VARCHAR(20),
    dob   VARCHAR(20)
);

If you created an index on the 'name' field this would create in a balanced tree for that data in the table for the name column. Balanced tree data structures allow for faster searching of results (see http://www.solutionhacker.com/tag/balanced-tree/).

You should note however indexing a column only allows you to search on the data as it is stored in the database. For example:

This would not be able to search on the index and would instead do a sequential scan on the table, calling UPPER() on each of the column:name rows in the table.

select *
from person
where UPPER(name) = "BOB";

This would also have the following effect, because the index will be sorted starting with the first letter. Replacing the search term with "B%" would however use the index.

select *
from person
where name like "%B"