MySQL Indexes

Types of indexes

A regular INDEX

PRIMARY KEY

FULLTEXT

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))
);
        

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

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'.