Image for post
Image for post

MYSQL: How to Use Indexes

At my current company ABOUT YOU, we have to deal with huge amounts of data, especially in our databases and often we have to check out our performance and costs over and over again, to get the last piece of improvement or DB size reduction.
One of the things doing the trick in most of our cases is the right usage of indexes and the knowledge about it.

In the following article, I explain indexes based on MYSQL 5.7 (more common) with InnoDB as an engine. This can also work for MariaDB >10 or other versions of MYSQL 5.7 and greater.

What is an Index?

If a database query uses an indexed column as a criteria, the internal Database Management System (DBMS) searches within the related index pointers. In general, indexes are using balanced trees (B-Trees) for that, and without an index, the columns need to be searched sequentially which can be slower depending on the number of rows in the table.

What Structures Do Indexes Use?

What is a Balanced Tree?

Unlike binary trees, in which each node has at most two children, balanced trees have many keys per node and this allows them to not grow “tall” or “deep” as quickly as a binary tree. But let's focus again on the index creation.

How to Create an Index?

CREATE INDEX Indexname ON Table ( Column(n) )

Usually, indexes are based on one column, but it is also possible to combine multiple columns.

CREATE INDEX user_firstname_lastname_idx ON user ( firstname, lastname )

An index with multiple columns can already improve your speed and database size drastically. Let us assume we have 100k products in our DB and your query looks like the following.

SELECT * FROM products WHERE is_deleted = 0 AND status = 'ordered';

One solution would be to create two single indexes on the columns is_deleted and status. This would make your query already faster, but in the long run, it will also increase your index size, because MYSQL has to create and maintain two separate indexes.

Your costs for your databases increases, if the size of the database is getting bigger and bigger. This will probably also affect the performance of the database.

This is why the second and sometimes better solution is to create a combined Index.
CREATE INDEX product_is_deleted_status_idx ON products (is_deleted, status )

Another benefit of the combined index is, that if you only want to query for is_deletedthe combined index from above still matches. Even if you have three or more columns bound to the index, as long as the order is the right one, you are good to go.

Matches Index:
SELECT * FROM products WHERE is_deleted = 0 AND status = 'ordered';
Misses Index
SELECT * FROM products WHERE status = 'ordered' AND is_deleted = 0;

But as always it comes with a drawback. As I already mentioned, to use this index the query parameters need to be in the same order as the columns of the index. Writing queries in a bigger team has to be organized and other members of the team need to be aware of the knowledge of indexes as well. Make sure to share new knowledge to make you and your team even better.

A good place for this is a team code review, where each developer can share knowledge and point out bottlenecks that can be improved together.

Analyze Your Application Queries!

This is why I came up with the following checklist for me:

  1. Enable MYSQL slog query logs, to see the queries which are really painful.
  2. Find reoccurring parameters within your queries belonging to the same table and bring them through a normal or combined index in the right order.
  3. Rewrite queries to match indexes and change the query of retrieving the data, if possible. (Team discussion)
  4. Remove unused or redundant indexes to save money and on top of it, the size of the database will be reduced. (see Did You Know)
  5. Do not add an index everywhere, as this can lead to new performance issues and also increases the size of the database. (see How Indexes effect Writing Operations)
  6. Share knowledge within your team to keep performance debugging to a minimum. Try to write performance optimized queries right away.
  7. Keep yourself up to date!

How to Analyze Queries For a Better Index Performance

As you probably know, I am talking about EXPLAIN. EXPLAIN also has aliases that do exactly the same. DESCRIBE or DESC.

The function is pretty handy because not only SELECT queries are working with indexes. You can use it to SELECT, DELETE, INSERT, REPLACE, and UPDATE statements as well.

It is also possible to just check the performance by tracking the execution time through variables in MYSQL.

But this only gives you a metric and does not deliver any information about the root cause of the performance bottlenecks. Therefore I still recommend the explain function and its output explanation.

How Indexes affect Writing Operations

An index can affect various types of operations such as ALTER TABLE, single and multiple INSERTS, UPDATE and even DELETE operations. This often happens if you are dealing with a highly frequented database that needs to handle a huge amount of read and write requests at the same time.

But why does it affect the writing operations? Because it has to update the index for the whole table and this is why it can make sense to disable the index generation if you know that there are a lot of write operations incoming during one process.

Another workaround is to disable the autocommit mode of InnoDB because it creates a log flush to disk for every insert.

Besides disabling autocommit, you can also do this for unique_checks and foreign_key_checks. The downside for the last two is the possibility of creating inconsistent data, so be aware of what you are about to do.

Did You Know?


I hope you enjoyed reading the article and could even get some useful information out of it.


Written by

Head of Backbone Panel Development / Cloud-Services Cloud-Panel at ABOUT YOU GmbH Employed since 01.06.2015

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store