Indexes in MySQL

Table of Content:

1. Indexes in general

2. Index types

1. Why do we use indexes?

Indexes are type of tables, which keep primary key or index field and a pointer to each record into the actual table. The users cannot see the indexes, they are just used to speed up queries and will be used by the Database Search Engine to locate records very fast. MySQL uses indexes for multiple purposes:

To run queries with WHERE clause faster.
Fast retrieving of rows from other tables when joining them.
Query optimization: GROUP BY, ORDER BY optimizations.
Fast retrieving of rows while selecting them.
Constraints
MAX(), MIN()

2. Index Types

MySQL has 2 storing engines: InnoDB and MyISAM. You can read more about their differences via this link.

2.1. Creation of Indexes.

There are 3 different ways to create indexes in MySQL.

Using CREAT INDEX keyword:

CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

While creating table with CREATE TABLE keyword:

CREATE TABLE table_name(
   column_1 CHAR(30) NOT NULL,
   INDEX (column_2));

Using ALTER TABLE keyword:

ALTER TABLE table_name ADD INDEX (column_1, column_2);

2.2. Types of Indexes.

There are 6 types of indexes in MySQL. Each of them serve for different purposes.

2.2.1 Unique

A unique index is a type of index which all column values have to be unique. In a single column unique index there can be no duplication of values in the column being indexed. In a multi-column unique index the values can be duplicated in a single column, but the combination of column values in each row must be unique. You use a unique index to prevent duplicate values and you often define the index after a table has been created. Below is how you can create unique index:

1. CREATE UNIQUE INDEX index_name
   ON table_name(index_column_1,index_column_2,…);

2. CREATE TABLE table_name(
   column_1 CHAR(30) NOT NULL,
   UNIQUE INDEX(column_2));

3. ALTER TABLE table_name ADD UNIQUE INDEX(column_1, column_2)

2.2.2 Primary Key

A primary key is a unique index in which no value can be NULL. Every row must have a value for the column or combination of columns. You would usually define a primary key on the smallest number of columns possible because of this, and most of the time a primary key will be set on a single column. Also, once set the column values in the primary key can’t be changed. Below is how you can create primary key index.

1. CREATE TABLE table_name(
   column_1 datatype PRIMARY KEY );

2. CREATE TABLE table_name(
   column_1 CHAR(30) NOT NULL,
   column_2 CHAR(30) NOT NULL,
   PRIMARY KEY(column_1, column_2));

3. ALTER TABLE table_name ADD PRIMARY KEY(column_1, column_2);

2.2.3 Simple, Regular, Normal

A simple, regular, or normal index is an index where the values don’t need to be unique and they can be NULL. They’re added simply to help the database search for things faster. Below is how you can create normal index.

1. CREATE INDEX index_name
   ON table_name(index_column_1,index_column_2,...);

2. CREATE TABLE table_name(
   column_1 CHAR(30) NOT NULL,
   INDEX(column_2));

3. ALTER TABLE table_name ADD INDEX(column_1, column_2);

2.2.4 Full-text

A full-text index, as the name implies, are used for full text searches. Sometimes you want to find the blob of text that contains a certain word or group of words or maybe you want to find a certain substring within the larger block of text. It is widely used in e-commerce and search engines. FULLTEXT indexes are supported only for InnoDB and MyISAM tables and can include only CHAR, VARCHAR, and TEXT columns. Below is how you can create full-text index.

1. CREATE FULLTEXT INDEX index_name
   ON table_name(index_column_1,index_column_2,...);

2. CREATE TABLE table_name(
   column_1 TEXT,
   FULLTEXT(column_1));

3. ALTER TABLE table_name ADD FULLTEXT (column_1, column_2);

2.2.5 Spatial

Spatial indexes are new in MySQL and is not widely used. MySQL permits creation of SPATIAL indexes on NOT NULL geometry-valued columns. SPATIAL INDEX creates an R-tree index. For storage engines that support nonspatial indexing of spatial columns, the engine creates a B-tree index. A B-tree index on spatial values is useful for exact-value lookups, but not for range scans. The optimizer can use spatial indexes defined on columns that are SRID-restricted. I am planning a whole new article about this index in near future. Below is how you can create spatial index.

1. CREATE SPATIAL INDEX index_name
   ON table_name(index_column_1);

2. CREATE TABLE table_name(
   column_1 GEOMETRY NOT NULL SRID 4326,
   SPATIAL INDEX(column_2));

3. ALTER TABLE table_name ADD SPATIAL INDEX(column_1, column_2);

2.2.6 Descending

A descending index which is available only 8+ versionof MySQL, is a regular index stored in reverse order. It’s helpful when you run queries for the most recently added data like you might to show your five most recent posts or the ten most recent comments on all your posts. Previously, indexes could be scanned in reverse order but performance was a problem. A descending index can be scanned in forward order, which is more efficient. Descending indexes also make it possible for the optimizer to use multiple-column indexes when the most efficient scan order mixes ascending order for some columns and descending order for others. Below is how you can create descending index.

1. CREATE INDEX index_name
   ON table_name(index_column_1 DESC);

2. CREATE TABLE table_name(
   column_1 INT,
   column_2 INT,
   INDEX asc_index_1 (column_1 ASC, column_2 ASC),
   INDEX desc_index_1 (column_1 DESC, column_2 DESC));

3. ALTER TABLE table_name ADD INDEX(column_1 DESC, column_2 ASC);

This is it for today’s article. I hope you enjoyed :).