Back to blog

How to Optimize MySQL Query Performance: Indexing and Slow Query Analysis

As your application grows, database performance bottlenecks are inevitable. A query that executes in 10ms on a development database containing 100 rows can easily choke your production database containing millions of rows, spiking server CPU usage to 100 percent and locking up web applications.

Optimizing relational databases like MySQL is not about guessing which query is slow. It requires a systematic approach to identifying slow operations, analyzing their execution plans, and configuring optimized indexes.

In this guide, we will explore how to locate slow MySQL queries, analyze them using the EXPLAIN command, and apply indexing best practices.

Step 1: Detect Slow Queries with Slow Query Logs

Before optimizing, you must find out which queries are slow. MySQL includes a built-in tool called the Slow Query Log that logs any query exceeding a specific execution time.

To enable it, open your MySQL configuration file (typically my.cnf or mysqld.cnf) and add these configurations:

# Enable the slow query log
slow_query_log = 1
# Define the path to the log file
slow_query_log_file = /var/log/mysql/mysql-slow.log
# Set the threshold in seconds (e.g., log any query taking longer than 1 second)
long_query_time = 1.00
# Log queries that do not use indexes
log_queries_not_using_indexes = 1

Once enabled, restart MySQL. You can inspect the log file directly or use analyzing tools like mysqldumpslow to group and list the slowest queries in your database.

Step 2: Analyze Execution Plans with EXPLAIN

Once you locate a slow query, prepend the EXPLAIN keyword to your SQL statement to see how the MySQL optimizer plans to execute it.

EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = 'completed';

The output returns a table layout. Pay close attention to these key columns:

  • type: Indicates the join type.
    • ALL: Full Table Scan. MySQL reads every row in the table to find matches. This is a major performance bottleneck for large tables.
    • index: Full Index Scan. Slower than target indexes, but faster than ALL.
    • ref or const: MySQL searches specific index entries. This is highly efficient.
  • key: Displays the index name MySQL chose to execute the query. If this is NULL, no index was used.
  • rows: Establishes the estimated number of rows MySQL must scan to find the matching results.
  • Extra: Displays optimization details. If you see Using filesort or Using temporary, it indicates MySQL must perform extra in-memory sorting, which degrades performance.

Step 3: Indexing Best Practices

Adding indexes is the most effective way to optimize queries. However, indexes must be designed carefully.

1. The Leftmost Prefix Rule for Composite Indexes

If you frequently query data using multiple fields (e.g., searching orders by both user_id and created_at), you should create a Composite Index (multiple columns in a single index).

CREATE INDEX idx_user_created ON orders(user_id, created_at);

To use this index, MySQL enforces the Leftmost Prefix Rule. The index can accelerate queries searching by:

  • user_id AND created_at
  • user_id only

However, it cannot accelerate queries searching by created_at only, because user_id (the leftmost column) is missing from the query condition.

2. Avoid Index Failure Pitfalls

Even if you have created an index, MySQL can ignore it under certain conditions:

  • Performing Column Operations: If you apply a function or mathematical operation to an indexed column, MySQL will skip the index.
-- Inefficient: Skips index
SELECT * FROM users WHERE YEAR(created_at) = 2026;

-- Efficient: Uses index
SELECT * FROM users WHERE created_at >= '2026-01-01' AND created_at <= '2026-12-31';
  • Implicit Type Conversion: If user_id is a string column, but you pass a number:
-- Inefficient: Skips index due to type mismatch coercion
SELECT * FROM users WHERE phone = 123456789;

-- Efficient: Uses index
SELECT * FROM users WHERE phone = '123456789';
  • Leading Wildcard Searches: Wildcard searches using LIKE only utilize indexes if the wildcard is at the end.
-- Inefficient: Skips index
SELECT * FROM users WHERE username LIKE '%alex';

-- Efficient: Uses index
SELECT * FROM users WHERE username LIKE 'alex%';

Conclusion

MySQL database tuning is an ongoing cycle. By enabling Slow Query Logs to identify bottlenecks, parsing the execution tree via EXPLAIN to isolate table scans, designing composite indexes using the leftmost prefix rule, and avoiding index suppression traps like type mismatches or column operations, you can keep your databases responsive and stable under heavy workloads.