Database Indexing

An index is an ordered (or indexed) subset of table columns, with each row entry pointing to its corresponding table row.

Introducing indexing into your MySQL database development strategy gives you three advantages:

  1. Query optimization: Data is stored in a table in the same order in which you enter it. However, this order may not coincide with the order in which you’d like to access it.
  2. Uniqueness: Often, a means is required for identifying a data row based on some value or set of values that is known to be unique to that row.
  3. Text searching: It is possible to optimize searching against even large amounts of text located in any field indexed as such.

Primary Key Indexes

The primary key index is the most common type of index found in relational databases. It is used to uniquely identify each row as a result of the primary key's uniqueness.

Therefore, the key must be either a value that the entity represented by the row uniquely possesses, or some other value such as an automatically incrementing integer value created by the database at the time of row insertion.

id INT UNSIGNED NOT NULL AUTO_INCREMENT,

Because the id column automatically increments (beginning with 1) with each insertion, it is not possible for the table to ever contain multiple rows containing exactly the same cells.

Unique Indexes

Like a primary index, a unique index prevents duplicate values from being created. However, the difference is that only one primary index is allowed per table, whereas multiple unique indexes are supported.

It is possible to designate multiple fields as unique in a given table. For example,

CREATE TABLE table_name (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(75) NOT NULL UNIQUE,
url VARCHAR(200) NOT NULL UNIQUE,
description MEDIUMTEXT NOT NULL,
PRIMARY KEY(id));

You can also specify a multiple-column unique index. For example, suppose you want to allow to insert duplicate URL values, and even duplicate name values, but you do not want duplicate name and URL combinations to appear. For example,

CREATE TABLE table_name (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(75) NOT NULL,
url VARCHAR(200) NOT NULL,
UNIQUE(name, url),
description MEDIUMTEXT NOT NULL,
PRIMARY KEY(id)); 

Normal Indexes

You often want to optimize a database's ability to retrieve rows based on column criteria other than those designated as primary or even unique. The most effective way to do so is by indexing the column in a way that allows the database to lookup a value in the fastest way possible. These indexes are typically called normal, or ordinary.

Single-Column Normal Indexes

A single-column normal index should be used if a particular column in your table will be the focus of a considerable number of your selection queries.

For example, suppose a table containing employee information consists of four columns: a unique row ID, first name, last name, and e-mail address. You know that the majority of the searches will be specific to either the employee’s last name or the e-mail address. You should create one normal index for the last name and a unique index for the e-mail address, like:

CREATE TABLE employees (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
firstname VARCHAR(100) NOT NULL,
lastname VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
INDEX (lastname),
PRIMARY KEY(id));

Multiple-Column Normal Indexes 

Multiple-column indexing is recommended when you know that a number of specified columns will often be used together in retrieval queries.

MySQL’s multiple-column indexing approach is based upon a strategy known as leftmost prefixing. Leftmost prefixing states that any multiple-column index including columns A, B, and C will improve performance on queries involving the following column combinations:

  • A, B, C
  • A, B
  • A

Here's how you create a multiple-column MySQL index:

CREATE TABLE employees (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
lastname VARCHAR(100) NOT NULL,
firstname VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
INDEX name (lastname, firstname),
PRIMARY KEY(id));

This creates two indexes (in addition to the primary key index). The first is the unique index for the e-mail address. The second is a multiple-column index, consisting of two columns, lastname and firstname. This is useful because it increases the search speed when queries involve any of the following column combinations:

  • lastname, firstname
  • lastname

Full-Text Indexes

Full-text indexes offer an efficient means for searching text stored in CHAR, VARCHAR, or TEXT datatypes. For example,

CREATE TABLE table_name (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(75) NOT NULL,
url VARCHAR(200) NOT NULL,
description MEDIUMTEXT NOT NULL,
FULLTEXT(description),
PRIMARY KEY(id));

In addition to the primary index, it creates a full-text index consisting of the description column. When retrieving data based on full-text indexes, SELECT queries use two special MySQL functions, MATCH() and AGAINST().