MySQL Indexes

Created for

Iva E. Popova, 2016,

Introduction

What and Why?

A database index is added to a table to speed up the search operation.
An index is used by the Database Search Engine to locate records very fast.
Indexes can be created for one or more columns.
Indexes are implemented as a type of tables, which keep index field and a pointer to each record into the actual table.
While adding index to a table will speed the SELECT queries, it will slow down the INSERT and UPDATE statement.

Types of indexes

A regular INDEX
PRIMARY KEY
FULLTEXT

Regular index

Considerations

Consider data in which columns will be searched most often.

Add regular Index to a table

The syntax:


ALTER TABLE table_name ADD INDEX(column_name(index_length));

The index_length could be specified to minimize the size of the index, and to optimize database access speed.
If index_length is specified, it should be long enough, because if MySQL finds two indexes with the same contents, it will have to waste time going to the table itself and checking the column that was indexed to find out which rows really matched.

Example

Let's have the artist table, defined as:

mysql music_db_admin@localhost:music_db> desc artist;
+---------+-------------+--------+-------+-----------+---------+
| Field   | Type        | Null   | Key   |   Default | Extra   |
|---------+-------------+--------+-------+-----------+---------|
| fname   | varchar(20) | YES    |       |    <null> |         |
| lname   | varchar(20) | NO     |       |    <null> |         |
+---------+-------------+--------+-------+-----------+---------+

An index could be added as:


ALTER TABLE artist ADD INDEX(lname(10));

Resulting in:

mysql music_db_admin@localhost:music_db> desc artist;
+---------+-------------+--------+-------+-----------+---------+
| Field   | Type        | Null   | Key   |   Default | Extra   |
|---------+-------------+--------+-------+-----------+---------|
| fname   | varchar(20) | YES    |       |    <null> |         |
| lname   | varchar(20) | NO     | MUL   |    <null> |         |
+---------+-------------+--------+-------+-----------+---------+

The MUL key means that multiple occurrences of a value may occur within that column.

Using CREATE INDEX

The syntax:


CREATE INDEX column_name ON table_name (column_name(index_length));

It is an alternative to adding an index with ALTER TABLE.
NB! They are equivalent, except that CREATE INDEX cannot be used to create a PRIMARY KEY

Adding indexes when creating tables

You should be aware that adding an index to a large table can take a very long time.

That's why is better to add the index when creating the table!


CREATE TABLE artist (
    fname VARCHAR(20) DEFAULT NULL,
    lname VARCHAR(20) NOT NULL,
    INDEX(lname(10))
);

Primary key

Why?

Regular indexes do not impose the values to be unique!

The importance of having a key with a unique value for each row will come up when we start to combine data from different tables.

Create

By adding the PRIMARY KEY(column_name) at the end of CREATE TABLE.


CREATE TABLE artist (
    id MEDIUMINT(4) NOT NULL AUTO_INCREMENT,
    fname VARCHAR(20) DEFAULT NULL,
    lname VARCHAR(20) NOT NULL,
    PRIMARY KEY(id)
);

Resulting in:

+---------+--------------+--------+-------+-----------+----------------+
| Field   | Type         | Null   | Key   |   Default | Extra          |
|---------+--------------+--------+-------+-----------+----------------|
| id      | mediumint(4) | NO     | PRI   |    <null> | auto_increment |
| fname   | varchar(20)  | YES    |       |    <null> |                |
| lname   | varchar(20)  | NO     |       |    <null> |                |
+---------+--------------+--------+-------+-----------+----------------+

NB! As PRIMARY KEY requires unique values, the NOT NULL and/or AUTO_INCREMENT attribute should be present.

Create - alternative notation

The AUTO_INCREMENT attribute

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.
A column defined with that attribute will set the value of its contents to that of the column entry in the previously inserted row, plus 1.

AUTO_INCREMENT - example


CREATE TABLE artist (
  artist_id SMALLINT(5) NOT NULL AUTO_INCREMENT KEY,
  fname VARCHAR(20) DEFAULT NULL,
  lname VARCHAR(20) NOT NULL
);

The KEY attribute should also be defined!

If it is not present, MySQL raises an error: 'Incorrect table definition; there can be only one auto column and it must be defined as a key'.