Indexing a column in a table is a process that stores information about the values in that column, so that it can be accessed more quickly.
Without an index, the only way to determine which rows in a table match the clause
where ddnaissance = 1975is to examine all of the rows in that table one at a time. On the example tables that we have used in this document, no performance problem will ever arise, and the execution of this kind of query will always be instantaneous. In real cases, it is not uncommon to encounter tables with hundreds of thousands of rows, or even more. In these cases, looking at the entire table row by row can lead to a dramatic slump in performance. The use of indexes is there to avoid this kind of degradation.
Under MySQL, index creation is automatic in the following cases:
- the creation of a primary key: an index is automatically created on any column that carries a primary key;
- the creation of a foreign key: an index is automatically created on any column that carries a foreign key.
- the declaration of a constraint
uniqueon a field: an index is automatically created on any column which carries unique values.
In all other cases, the creation of an index must be done manually.
The creation of an index is done by the following command.
Example 80. Creating an index
CREATE INDEX index_name ON table_name (column_name [, column_name])
The existence of an index greatly accelerates the queries that can use them, sometimes by factors greater than 1000. On the other hand, maintaining an index on a column has an impact on the writes of that column. However, in practice, this impact remains small compared to the gains made.