Last Updated:

What is indexing in MySQL? | With Example

MySQL

In the work of the project, a very important factor is the speed of its work.
One of the methods to improve the speed of the project is: to speed up the execution time of the MySql database query, by reducing the response time when executing a query to the database.

Today we will get acquainted with such a tool as "Indices".

The use of indexes will reduce the response time from the MySql database, which gives us huge advantages when selecting data from a large database and will be of great benefit when used in large, high-load projects.

Let's take a look at what indexes are, what indexes are, and what benefits can be derived from them.

MySql Index is an important and useful tool that allows you to optimize the selection from the database. Using indexes significantly reduces query execution time, allowing you to retrieve data faster.

A noticeable difference when using MySql indexes can be seen when working with large tables.

An index is a structure that stores the values of one or more columnsof the table and references to the rows where those values are located. "Binary trees" are most often used to store indexes.

Consider an example of how indexes work:

You can experience the benefits of how indexes work when you run a SELECT query with the WHERE condition.
We have a table that stores information about users.
The table has columns:

  • id - autoincrement
  • firstname - Name
  • lastname - Surname
  • age - Age

The first id field is an auto-incrementing and always unique field, so we assign an index to it by default.

Now, according to this table, we will make a sample of all users whose age is 20 years.
This query will look like this:

This query does not look complex and should not have a load, but if your database is larger than 1000 rows and the number of such queries is more than one, then the execution of this query will already put a load on the database and the time spent on sampling will no longer be what was expected.

And now let's run this query on a database with a span of 600mb and containing 15 million rows.

And so, the request is running and the execution time of this request is - 10.562 seconds.

I repeat the execution of this query 2 more times, in order to understand the average time of execution of the request:

Second run - 10.359
Third run - 10.715
SELECT * FROM users WHERE age = 20;


Agree that waiting for 10 seconds for the result of the request is quite a long time.
Now let's define the "index" on the age column and retry the query.

The query execution time was - 0.031 seconds, which is much faster than it was before.
Thus, we see a significant increase in speed when using indices.


Types of MySql indexes:
  • PRIMARY KEY : A primary key that allows you to store your values as unique table entries. This type of key must be present in the table in a single instance. Typically, this type of key is defined in a column named id.
  • UNIQUE - Unique key: Partially similar to the primary key, due to the fact that the values of the table column must be unique (should not be repeated) and should not be null.
  • Composite index: This type of index includes several fields in the indexing. This type of indexing is typically used in queries where you want to fetch multiple fields (where more than one parameter occurs in the WHERE condition).

Composite Index Sample:How do I

create an index in MySql?

There are several ways to create an index.

Below is an example of how to create an index using MySql database administration software - PhpMyAdmin and how to create an index using a database query.
SELECT * FROM users WHERE age = 20 AND firstname = 'Alex';

  1. Issuing an index using PhpMyAdmin. - Here is the structure of the previously created database with the users table, which contains the fields id - primary key, firstname - first name, lastname - last name, age - age.
    You can select multiple columns in a table and mark them as an index.

    Or, on the column you are interested in on the right side, hover over the "More" field and select the "Index" field in the drop-down list
     
     
  2. Assign an index by using a query:

    Example. Add an index to the users table on the age column. The name of the index is NEWINDEX.
    ALTER TABLE `Table_name` ADD INDEX [Index_name] (`Column_name`, `or_columns_separated by_commas`...)

    ALTER TABLE `users` ADD INDEX NEWINDEX (`age`);
 

How to create a unique index in MySql?

 

An example of creating a unique index using phpMyAdmin and creating a unique index using a query:

  1. Issuing an index using PhpMyAdmin. - Here is the structure of the previously created database with the users table.

    You can select several columns of the table you need and mark them as a unique index.

    Or hover over the "More" tab against the required field and select "Unique" from the drop-down list.
     
     
  2. Assign a unique index by using a query:

    Example. Assign a unique index to the users table on the age column.
    ALTER TABLE `TableName` ADD UNIQUE(`ColumnName`);

    ALTER TABLE `users` ADD UNIQUE(`age`);
 


How do I create a composite index in MySql?

 

An example of creating a composite index using phpMyAdmin and creating a composite index using a query:

  1. Output a composite index using phpMyAdmin. -
    Let's take it as a condition that we should have a request for 2 columns of the database.
    The first column is lastname, the second column is age.
    To create a composite index, go to the "Structure" page and in the "Indexes" table in the "Create index for" field, enter the number of columns we need (For the current example, you need to create 2 columns) and click the "Forward" button.
    A modal window should open in which you need to mark the required columns and size.

    Assign a composite index to the "lastname" and "age" fields. Assign this index the name "TEST" and the index type "INDEX". For example, this is enough, now click the "Run" button.

    A composite index is created.
     
     
  2. Assign a composite index by using a query:

    Example. Add a composite index to the users table on the lastname and age column. The name of the index is TEST.
    ALTER TABLE `TableName` ADD INDEX `IndexName` (`ColumnName_n`, `ColumnName_n+1`);

    ALTER TABLE `users` ADD INDEX `TEST` (`lastname`, `age`);

How to delete MySql index?
  1. Deleting an index using phpMyAdmin:
    phpMyAdmin can just as easily delete indexes. You can delete indexes on the Table Structure page.
    Below the table with the structure will be a table called indexes.
    To delete the index, you need to find the column you are interested in and click on the "Delete" button.

     
  2. Delete an index by using a query:

    Example. Delete the index from the age column, the users table.
    ALTER TABLE ``Table Name`` DROP INDEX ``Column Name``;

    ALTER TABLE ``users`` DROP INDEX ``age``;

A number of disadvantages when using indexes:


If indexes give such an increase during the execution of queries, so why not define indexes on all columns of the table?

  1. In the process of indexing the columns of MySql tables, additional tables are created in which the sorted data is stored. And such data takes up a certain amount of physical memory.
  2. When running a number of queries, the presence of indexes can play the exact opposite role and increase the execution time of the query.
  3. If there is an index in a table, an INSERT query takes longer than a table that does not contain indexes.
  4. Using indexes can also have a negative effect when executing a query using the LIKE statement in the WHERE clause. For example, using the LIKE statement on an indexed field will not give any increase in execution speed.


We have analyzed only a part of the main disadvantages when using indices. The actual number may vary.