In this article, I discuss about query optimization using database indexes.
I discuss about deciding whether you should use database indexes on your tables, types of indexes in MySQL, how to list existing indexes on your tables, how to create indexes and how to delete indexes.

Indexes are used to find rows with specific column value(s) faster.

Indexes in MySQL can be created on a single column:

CREATE INDEX <indexname> ON <tablename> (column1);

or multiple columns(composite index):

CREATE INDEX <indexname> ON <tablename> (column1, column2, ...);

An index may consist of up to 16 columns.

Deciding Which Indexes to Create

Before you create indexes for a database table, consider how you will use the table. The two most common operations on a table are to:

• Insert, update, and delete records
• Retrieve records

If you most often insert, update, and delete records, then the fewer indexes associated with the table, the better the performance. This is because when querying the database, the driver must maintain the indexes and also the database tables, thus slowing down the performance of query operations( inserts, updates, and deletes ).

If you most often retrieve records, you must look further to define the criteria for retrieving records and create indexes to improve the performance of these retrievals. Assume you have an employee database table and you will retrieve records based on employee name, department, or hire date. You would create three indexes-one on the DEPT field, one on the HIRE_DATE field, and one on the LAST_NAME field. Or perhaps, for the retrievals based on the name field, you would want an index created on multiple columns(multiple-column indexes) that concatenates the LAST_NAME and the FIRST_NAME fields (see "Indexing Multiple Fields" for details).

Managing Indexes

Showing Indexes

Tables can have multiple indexes. The query to list the existing indexes on a table is:

SHOW INDEX FROM <tablename>;

Creating Indexes

In MySQL, we have three different methods for creating indexes:

At the time of table creation

• Using keyword CREATE TABLE to create a table

  CREATE TABLE <tablename> (
  ID int,
  LName varchar(255),
  FName varchar(255),
  DOB varchar(255),
  LOC varchar(255),
  INDEX ( ID )
  );

After table has been created

• Using keyword CREATE INDEX.

  CREATE INDEX <indexname> ON <tablename> (column1, column2, ...);

• Using keyword ALTER TABLE.

  ALTER TABLE <tablename> ADD INDEX (column1, column2, ...);

Types of Indexes

note: KEY is a synonym for INDEX

1. Unique Index A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new record with column value matching existing column value. A UNIQUE index permits multiple NULL values for columns that can contain NULL. You can create more than one UNIQUE index per table.

how it's created:

   CREATE UNIQUE INDEX <indexname> ON <tablename>(column1, column2, ...);

or

   CREATE TABLE <tablename> (
   column1 datatype,
   column2 datatype,
   column3 datatype,
   column4 datatype,
   column5 datatype,
   ... ,
   UNIQUE KEY (column1, column2, ...)
   );

note: when you create a unique constraint, MySQL creates associated unique index behind scenes.

or

   ALTER TABLE <tablename> ADD CONSTRAINT <constraintname> UNIQUE KEY(column_1, column_2, ...);

1. Primary Key

A primary key is a column or a set of columns that uniquely identifies each row in the table.
It is a type of unique index that does not permit NULL values for columns.

It has an associated index, for fast query performance.
We can only have one primary key in a database table.

If the primary key consists of multiple columns, the combination of values in these columns must be unique.

how it's created:

If the primary key has one column:

   CREATE TABLE table_name(
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype,
    ...
   );

If the primary has multiple columns:

   CREATE TABLE <tablename>(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...,
    PRIMARY KEY(column1, column2, ...)
   );

or

   ALTER TABLE <tablename> ADD PRIMARY KEY(column1, column2, ...);

1. Simple index

Also known as Regular or Normal Index.
For the simple index, the specified column values do not require to be unique and as well as can be NULL also. They are supplemented basically to aid the database searching process for records quicker.

how it's created:

   CREATE INDEX <indexname> ON <tablename>(column1, column2, ...);

OR

   CREATE TABLE <tablename>(
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ...,
    INDEX(column1, column2, ...)
   );

OR

   ALTER TABLE <tablename> ADD INDEX(column1, column2, ...);

1. Full-Text Index

FULLTEXT index is used for full-text searches. This type of index is supported in columns with datatypes of CHAR, VARCHAR or TEXT. Only the InnoDB and MyISAM storage engines support FULLTEXT indexes.

Occasionally, suppose you need to find the blob of text which includes a certain word or combination of words, or it can even be a substring found within the higher block text, then this full-text index is better applicable here. It is broadly implemented in search engines and e-commerce.

how it's created:

   CREATE FULLTEXT INDEX <indexname> ON <tablename>(column1, column2, ...)

OR

   CREATE TABLE <tablename>(
       column1,
       column2,
       column3,
       ...,
       FULLTEXT (column1, column2, ...)
   );

OR

   ALTER TABLE <tablename> ADD FULLTEXT(column1, column2, ...)

Deleting Indexes

While managing indexes, you may find it necessary to remove some. Below is how:

DROP INDEX <indexname> ON <tablename>;