MySQL Index: Create, Retrieve and Remove

Indexes are a performance construct. They are applied to a particular column to help retrieve or access records faster. Without an Index, the RDBMS needs to scan the entire table every time for fetching records.

An Index is a data structure like a B-Tree that improves the speed of searching with minimum required searches.

MySQL automatically creates some indexes during Table creation. These indexes are known as cluster indexes. This index is created on the Primary Key of a table. We can create indexes on other columns and these are called Secondary indexes.

How to create an index in MySQL?

To create an Index on a table, we use the CREATE INDEX statement. The Basic syntax is

CREATE INDEX <index_name> ON <table_name> (<column_list>)

Example:

CREATE INDEX first_name_index ON employees(first_name);

The default index type that gets created in MySQL is BTree.

How to display all indexes in MySQL?

To get all the indexes present on a table, we use the Show indexes statement.

The basic syntax is,

SHOW INDEXES FROM <table_name>;

Example:

SHOW INDEXES FROM employees;

How to remove an index in MySQL?

To remove an index we use the DROP index statement. The Basic syntax of the same is

DROP INDEX <index_name> ON <table_name>
[algorithm_option | lock_option];

The Algorithm and the local options are optional.MySQL allows The default, INPLACE, and COPY algorithms.

INPLACE is the default algorithm used. This drop indexes and builds the table again without copying it to another table. While the Inplace drop index is happening, we can do insert and update transactions on the table.

The Copy algorithm copied the contents of the table row by row to another table and then drop indexes from the original table. Using this algorithm locks the tables and we cannot perform updates or inserts on it.

The lock option is used to put a lock on the table to allow or disallow reads and writes on the table. The options for locking are None, Shared, Default, and exclusive.

An example of a Drop index is,

DROP INDEX last_name_index from emloyees;

To drop a Primary key index we do,

DROP INDEX `PRIMARY` On <table-name>;

Please note the quotes are not the apostrophe symbol, they are the ACCENT GRAVE i.e. `

Example:

DROP INDEX `PRIMARY` On titles2;

If the column for which the Primary Key index is to be dropped,  is used as a foreign key in other tables, MySQL does not allow dropping it.

For example, The emp_no is a foreign key for a number of tables.

DROP INDEX `PRIMARY` ON employees;

Invisible Index in MySQL

An index on a column can be visible or invisible. To create an invisible index, we use the same create index syntax with the word Invisible added. Invisible indexes are used to check the performance characteristics of the table. Primary Key indexes cannot be Invisible, MySQL does not allow it.

Example: We will try to make the employees primary key index invisible

alter table employees
ALTER INDEX `PRIMARY` INVISIBLE;

USE Index in MySQL

We can suggest MySQL to use a particular hint while executing queries. Whether to use the particular hint or not is up to the MySQL Query optimizer. The basic syntax of using a hint is

SELECT select_list
FROM table_name USE INDEX(index_list)
WHERE condition;

Example:

select * from employees USE INDEX(first_name_index)
where first_name like 'A%';

We do not see any changes in the output of the Query. To find which index was used we can use the Explain plan

EXPLAIN  select * from employees USE INDEX(first_name_index)
where first_name like 'A%';

The image shows that the index first_name_index is used.

Example:

select * from employees USE INDEX(first_name_index)
order by emp_no desc;

Here the index we suggest is not used.