MySQL Indexing Explained: A Practical Guide for Backend Developers

Published 2026-05-17 · By Shubham Bhati · Backend Engineer at AlignBits LLC

Mysql Indexing Explained

Published 2026-05-17 by Shubham Bhati — Backend Engineer (Java 17, Spring Boot, Microservices).

We've all been there - stuck with a slow MySQL query that's bringing down our entire application. In our production environment, we once had a query that was taking over 800ms to execute, causing our p99 latency to skyrocket. After some digging, we realized that the issue was due to a lack of proper indexing. This experience taught us the importance of understanding mysql indexing explained and how it can drastically improve query performance. By applying the right indexing strategies, we were able to reduce our p99 latency from 800ms to 120ms.

Introduction to Indexing
Types of Indexes
BTree Index
Composite Index
Indexing in Practice
Common Mistakes
FAQ
Conclusion

Introduction to Indexing

When it comes to optimizing MySQL queries, indexing is one of the most effective techniques. An index is a data structure that improves the speed of data retrieval by providing a quick way to locate specific data. In MySQL, indexes are used to speed up the execution of SELECT, UPDATE, and DELETE queries. We can create an index on one or more columns of a table, and MySQL will use this index to quickly locate the required data. For example, let's consider a simple table with a column id and name:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

We can create an index on the name column using the following query:

CREATE INDEX idx_name ON users (name);

This index will allow MySQL to quickly locate rows with a specific name value.

Types of Indexes

MySQL supports several types of indexes, including BTree, Hash, and Full-Text indexes. Each type of index has its own strengths and weaknesses, and the choice of index type depends on the specific use case. For example, BTree indexes are suitable for range queries, while Hash indexes are suitable for equality queries. We can use the following query to create a BTree index on the name column:

CREATE INDEX idx_name ON users (name) USING BTREE;

In Java, we can use the Hibernate library to create indexes on our database tables. For example:

@Entity
@Table(name = "users")
public class User {

  @Id
  @Column(name = "id")
  private int id;

  @Column(name = "name")
  @Index(name = "idx_name")
  private String name;
}

BTree Index

BTree indexes are the most commonly used type of index in MySQL. They are suitable for range queries and provide efficient insertion, deletion, and search operations. BTree indexes are self-balancing, which means that they maintain a balanced tree structure even after insertion or deletion of nodes. This ensures that search, insertion, and deletion operations are always performed in logarithmic time. For example, let's consider a table with a BTree index on the id column:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

CREATE INDEX idx_id ON orders (id) USING BTREE;

We can use the following query to retrieve all orders for a specific customer:

SELECT * FROM orders WHERE id BETWEEN 100 AND 200;

This query will use the BTree index on the id column to quickly locate the required orders.

Composite Index

A composite index is an index that is created on multiple columns of a table. Composite indexes are useful when we need to query a table based on multiple columns. For example, let's consider a table with a composite index on the customer_id and order_date columns:

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE
);

CREATE INDEX idx_customer_id_order_date ON orders (customer_id, order_date);

We can use the following query to retrieve all orders for a specific customer and date range:

SELECT * FROM orders WHERE customer_id = 100 AND order_date BETWEEN '2020-01-01' AND '2020-12-31';

This query will use the composite index on the customer_id and order_date columns to quickly locate the required orders.

Indexing in Practice

In our production environment, we have a table with over 10 million rows, and we need to query this table frequently based on multiple columns. We created a composite index on the relevant columns, and this reduced our query execution time from 500ms to 50ms. We also use the EXPLAIN statement to analyze the execution plan of our queries and identify opportunities for optimization. For example:

EXPLAIN SELECT * FROM orders WHERE customer_id = 100 AND order_date BETWEEN '2020-01-01' AND '2020-12-31';

This statement will provide us with information about the indexes used, the number of rows scanned, and the execution time.

Indexing with Java and Spring Boot

When using Java and Spring Boot, we can use the Spring Data JPA library to create indexes on our database tables. For example:

@Entity
@Table(name = "orders")
public class Order {

  @Id
  @Column(name = "id")
  private int id;

  @Column(name = "customer_id")
  private int customerId;

  @Column(name = "order_date")
  private Date orderDate;

  @Index(name = "idx_customer_id_order_date")
  private void createIndex() {
    // Create index on customer_id and order_date columns
  }
}

We can also use the Hibernate library to create indexes on our database tables. For example:

@Configuration
@EnableJpaRepositories
public class DatabaseConfig {

  @Bean
  public DataSource dataSource() {
    // Create data source
  }

  @Bean
  public EntityManagerFactory entityManagerFactory() {
    // Create entity manager factory
  }

  @Bean
  public HibernateProperties hibernateProperties() {
    // Create hibernate properties
    HibernateProperties properties = new HibernateProperties();
    properties.put("hibernate.hbm2ddl.auto", "update");
    return properties;
  }
}

For more information on using Hibernate with Spring Boot, please refer to the official Hibernate documentation.

Common Mistakes

Here are some common mistakes to avoid when using indexes:
* Creating too many indexes on a table, which can slow down write operations
* Creating indexes on columns that are not frequently used in queries
* Not maintaining indexes regularly, which can lead to index fragmentation
* Using the wrong type of index for a particular use case
* Not monitoring index usage and adjusting indexing strategy accordingly

FAQ

What is the difference between a BTree index and a Hash index?

A BTree index is suitable for range queries and provides efficient insertion, deletion, and search operations. A Hash index is suitable for equality queries and provides fast lookup, but can be slower for range queries.

How do I create an index on a column in MySQL?

You can create an index on a column in MySQL using the CREATE INDEX statement. For example: CREATE INDEX idx_name ON users (name);

What is the purpose of the EXPLAIN statement in MySQL?

The EXPLAIN statement is used to analyze the execution plan of a query and identify opportunities for optimization. It provides information about the indexes used, the number of rows scanned, and the execution time.

How do I monitor index usage in MySQL?

You can monitor index usage in MySQL using the SHOW INDEX statement. For example: SHOW INDEX FROM users;

Conclusion

In conclusion, indexing is a powerful technique for optimizing MySQL queries. By understanding the different types of indexes and how to create them, we can significantly improve the performance of our applications. We can use the EXPLAIN statement to analyze the execution plan of our queries and identify opportunities for optimization. By avoiding common mistakes and monitoring index usage, we can ensure that our indexing strategy is effective and efficient. For more information on indexing in MySQL, please refer to the official MySQL documentation.

Mysql Indexing Explained in production