Course 23: Index in SQL

Oct 3, 2021
Course 23: Index in SQL

1. Introduction

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.

2. Index manipulation



2.1. Automatic index creation

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.



2.2. Manual index creation



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.

Leave a Reply

Your email address will not be published. Required fields are marked *