How and When to Create an Index in MySQL

When it comes to performance optimization for MySQL databases, creating indexes is one of the best ways. SQL indexes help improve database performance by providing fast access to the data stored in the database.

An SQL index is not that different from a normal book index with a curated list of information and where you can find them.

In this tutorial, we will discuss when to create an SQL index because, although indexes can help optimize performance, they can also cause slow performance and other negative impacts. We shall also discuss how to create an index using a real database.

Let us get started:

When To Create An Index In MySQL?

Unfortunately, I have no direct answer for when you should create an index. However, some scenarios and factors may influence the creation of an index. It is good to note that you may have to do some trade-offs when creating an index

High Accessibility: If you have a table or column accessed regularly, you may increase its performance by creating an index.
Size: The size of the data stored in a table or column may also play a role in deciding when an index is necessary. A large table may benefit more from indexes than a smaller table.
Index Key: The data type of the integer key is also a factor. For example, an integer is a much better index key due to its small size.
CRUD Operations: If you have a table or column with a higher number of CRUD operations, indexing that table or column might not be beneficial and might negatively impact database performance.
Database Size: Indexing is a data structure that will occupy space on your database, which can be a factor, especially in already large databases.

The above are some of the key concepts that may come into play when deciding when to create a database index.

If you would like to learn more about how MySQL uses indexes, consider reading the resource provided below:

https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

Basic MySQL Indexes Usage

By default, once you create a table with a primary key, MySQL automatically creates a special index called PRIMARY, stored with the data. This is typically known as a clustered index.

To create an index in MySQL, we can use the syntax shown below:

CREATE INDEX index_name ON tbl_name (cols);

We start by calling the CREATE INDEX clause, followed by the name of the index we wish to create. We then specify the table where the index resides and finally the columns.

You can also create an index when creating a table, as shown in the syntax below:

CREATE TABLE tbl_name (col1, col2, col3, INDEX(col1, col2));

NOTE: The default MySQL index type is a BTREE unless explicitly specified.

Example Use case

Using an example, allow me to illustrate how we can create an index for the specific table to enhance performance.

I will be using the film table in the employees’ database. You can find the resource from the resource page below:

https://dev.mysql.com/doc/index-other.html

First, let us see the behind-the-scene process MySQL uses to process a simple SELECT statement where the gender is equal to F.

EXPLAIN SELECT emp_no, first_name, last_name FROM employees WHERE gender = 'F';

Given the size of the table and queried data, scanning more than 200,000 rows is not very efficient. In that case, we can reduce this value by creating an index.

To create an index, we can do:

CREATE INDEX gender on employees(gender);

Once we have the index created, MySQL will scan the values as shown in the output above.

Conclusion

I hope this tutorial has given you a deeper understanding of using MySQL indexes to enhance database performance.

Thank you for reading.

How and When to Create an Index in MySQL

When To Create An Index In MySQL?

Basic MySQL Indexes Usage

Example Use case

Conclusion

About the author

John Otieno