Understanding MySQL Indexes and Query Optimization

Welcome dear reader, today you will learn how to speed up your database with MySQL indexes and query optimization. If you have ever wondered why some of your database queries feel slow, the answer often lives in the way your tables are indexed. In this guide, we will walk through the basics of indexing, and we will see how the EXPLAIN statement can help you understand what MySQL is doing under the hood.

Table of contents

• What is a MySQL Index and Why Do You Need It?
• How to Create Indexes on Your MySQL Tables
• Inspecting Queries with the EXPLAIN Statement
• Conclusion

What is a MySQL Index and Why Do You Need It?

An index in MySQL works a lot like the index at the end of a book. Instead of flipping through every page to find a topic, you simply look up the keyword in the index and jump straight to the right page. In a database, an index is a separate data structure that lets MySQL find rows quickly, without scanning the whole table.

Without an index, MySQL performs what is called a full table scan. It reads every single row to find the ones that match your WHERE clause. On a small table this is fine, but as your data grows, a full table scan becomes expensive and your queries start to slow down.

You typically need an index on the columns that you use frequently in WHERE, JOIN, and ORDER BY clauses. For example, if you often search users by their email address, an index on the email column will make those lookups almost instant.

How to Create Indexes on Your MySQL Tables

Creating an index is straightforward. The simplest way is to use the CREATE INDEX statement, as shown below:

-- Add a single-column index to an existing table
CREATE INDEX idx_users_email ON users(email);

You can also create an index directly when you create the table, using the INDEX keyword inside your CREATE TABLE statement:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_users_email (email)
);

A composite index covers more than one column, and it is useful when you often filter by several columns together:

-- Add a composite index on two columns
CREATE INDEX idx_users_name_email ON users(name, email);

Keep in mind that indexes are not free. Every index takes up storage space and must be updated whenever you insert, update, or delete rows. The general rule is to index the columns you search on, and avoid indexing columns that you rarely query.

Inspecting Queries with the EXPLAIN Statement

Once your indexes are in place, you want to make sure MySQL actually uses them. This is where the EXPLAIN statement comes in. Place the keyword EXPLAIN in front of any SELECT query, and MySQL will tell you how it plans to execute it.

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

The output contains several columns, but beginners should focus on two of them:

• type: tells you how MySQL looks up rows. A value of ALL means a full table scan, which you usually want to avoid. A value of ref or eq_ref means MySQL is using an index.
• key: shows which index MySQL chose. If this is NULL, no index was used.

If you see type: ALL and key: NULL, your query is doing a full table scan. Adding an index on the column used in your WHERE clause usually fixes it.

Conclusion

That’s already it for an introduction to MySQL indexes and query optimization. Remember to index the columns you search on, and use EXPLAIN to verify that MySQL actually takes advantage of your indexes. With these two habits, you will keep your database fast even as your data grows. Leave a comment below if you have any questions or feedback about the content of this article.

At re:Software S.L., we create innovative solutions for businesses that aim to digitize their processes and routines, and this topic today is part of one of our stories about digitization.