When your web application starts to crawl, the database is often the culprit. A single poorly optimized query can bring an entire site to its knees, especially under load. The good news is that MySQL gives you the tools to find exactly which queries are slowing you down and fix them systematically.
This guide walks you through three essential steps: enabling the slow query log to capture problem queries, using EXPLAIN to understand what MySQL is actually doing, and adding indexes that make your queries fast. These techniques work across shared hosting, VPS, and dedicated servers.
Understanding MySQL Slow Queries
A slow query is any SELECT, UPDATE, DELETE, or INSERT statement that takes longer than your threshold to execute. What counts as "slow" depends on your application—an e-commerce checkout might need sub-second responses, while a nightly report can afford to take minutes.
MySQL slow queries typically fall into a few patterns:
- Full table scans: MySQL reads every row instead of using an index
- Missing indexes: No efficient path exists to find the requested data
- Inefficient joins: Joining large tables without proper indexes on join columns
- Suboptimal query structure: Using functions on indexed columns or other patterns that prevent index use
The slow query log is your first diagnostic tool. It records every query that exceeds your configured time threshold, along with metadata like execution time and rows examined.
Enabling the Slow Query Log
MySQL's slow query log is typically disabled by default because it adds overhead. Enable it when you're investigating performance issues, then disable it once you've identified and fixed the problems.
Temporary Activation (Current Session)
You can enable slow query logging for your current session without restarting MySQL:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow-query.log';
This configuration logs any query taking longer than 2 seconds. Adjust long_query_time based on your needs—set it to 1 second or even 0.5 for web applications where fast response matters.
Permanent Configuration
To make slow query logging persistent across restarts, add these lines to your MySQL configuration file. On most Linux systems, this is /etc/my.cnf or /etc/mysql/my.cnf:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1
The log_queries_not_using_indexes option is particularly useful—it logs any query that performs a full table scan, even if it completes quickly. These queries often become problems as your data grows.
Restart MySQL to apply the configuration:
sudo systemctl restart mysqld
On cPanel servers, the path might be /etc/my.cnf and the service name might be mysql instead of mysqld.
Checking Log Location and Permissions
Verify your slow query log is working:
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
Make sure the MySQL user has write permissions to the log file directory. If you see permission errors in your MySQL error log, adjust ownership:
sudo chown mysql:mysql /var/lib/mysql/slow-query.log
Reading the Slow Query Log
The raw slow query log contains detailed information about each slow query. Here's what a typical entry looks like:
# Time: 2026-06-23T14:32:11.442Z
# User@Host: webapp[webapp] @ localhost []
# Query_time: 3.341251 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 458392
SELECT * FROM orders WHERE customer_email = '[email protected]' ORDER BY created_at DESC;
Key metrics to watch:
- Query_time: Total execution time—this is what triggered the log entry
- Lock_time: Time spent waiting for table locks
- Rows_examined: How many rows MySQL scanned—high numbers indicate missing indexes
- Rows_sent: How many rows were actually returned to the client
When Rows_examined is much larger than Rows_sent, you're scanning far more data than necessary. This query examined over 450,000 rows to return a single result—a clear sign of a missing index.
Using pt-query-digest
For production systems with thousands of queries, manually reading logs is impractical. The pt-query-digest tool from Percona Toolkit aggregates and ranks slow queries:
pt-query-digest /var/lib/mysql/slow-query.log > slow-query-report.txt
This generates a report showing which queries consume the most total time, which are slowest individually, and which patterns appear most frequently. Focus your optimization effort on queries at the top of the report—fixing one common slow query often has more impact than optimizing ten rare ones.
Understanding EXPLAIN Output
Once you've identified a slow query, EXPLAIN shows you exactly how MySQL executes it. Run EXPLAIN before the query:
EXPLAIN SELECT * FROM orders WHERE customer_email = '[email protected]' ORDER BY created_at DESC;
The output includes several critical columns:
- type: The join type, from best to worst:
const,eq_ref,ref,range,index,ALL - possible_keys: Which indexes MySQL considered
- key: Which index MySQL actually used (NULL means no index)
- rows: Estimated number of rows MySQL will examine
- Extra: Additional information like "Using filesort" or "Using temporary"
What to Look For
Bad signs in EXPLAIN output:
- type: ALL: Full table scan—MySQL is reading every row
- key: NULL: No index is being used
- rows: large number: MySQL expects to scan many rows
- Extra: Using filesort: MySQL must sort results in memory or on disk
- Extra: Using temporary: MySQL creates a temporary table for the operation
Good signs:
- type: const or eq_ref: Very fast lookups using primary or unique keys
- type: ref: Using a non-unique index efficiently
- key: index_name: An index is being used
- rows: small number: MySQL expects to examine few rows
For our slow orders query, you'd likely see type: ALL and key: NULL, confirming that no index exists on customer_email.
EXPLAIN FORMAT=JSON
For complex queries with multiple joins, the tabular EXPLAIN can be hard to read. Use JSON format for better structure:
EXPLAIN FORMAT=JSON SELECT ...
This shows the query execution plan as a nested JSON document, making it easier to trace how MySQL processes joins and subqueries.
Adding the Right Indexes
Indexes are data structures that let MySQL find rows quickly without scanning entire tables. Think of them like a book's index—instead of reading every page to find a topic, you look it up and jump directly to the relevant pages.
Single-Column Indexes
For queries filtering on one column, add a single-column index:
CREATE INDEX idx_customer_email ON orders(customer_email);
This index lets MySQL quickly find all orders for a specific email address. After adding the index, re-run your EXPLAIN and you should see the index name in the key column and type: ref.
Composite Indexes
Queries that filter on multiple columns or filter and then sort often benefit from composite (multi-column) indexes. Column order matters significantly.
For this query:
SELECT * FROM orders
WHERE customer_email = '[email protected]'
AND status = 'completed'
ORDER BY created_at DESC;
A composite index works best:
CREATE INDEX idx_customer_status_date ON orders(customer_email, status, created_at);
MySQL can use this index to find matching rows and return them already sorted. The general rule: put equality conditions first, range conditions next, and sort columns last.
Covering Indexes
A covering index includes all columns your query needs, letting MySQL satisfy the entire query from the index without touching the table data:
CREALTE INDEX idx_covering ON orders(customer_email, created_at, order_total, status);
When this index covers your query, EXPLAIN will show Using index in the Extra column—the fastest possible outcome.
Index Maintenance Considerations
Indexes aren't free:
- Each index increases storage requirements
- INSERT, UPDATE, and DELETE operations must update all relevant indexes
- Too many indexes can slow down writes more than they speed up reads
Focus on indexing columns used in:
- WHERE clauses (especially high-frequency queries)
- JOIN conditions
- ORDER BY clauses
- GROUP BY clauses
Avoid indexing:
- Columns with very low cardinality (few distinct values) unless combined in composite indexes
- Columns rarely used in queries
- Very wide columns (large TEXT or BLOB fields)
Verifying Your Optimization
After adding indexes, verify the improvement:
- Run EXPLAIN again—check that MySQL now uses your index
- Check query execution time directly:
SET profiling = 1;
SELECT * FROM orders WHERE customer_email = '[email protected]';
SHOW PROFILES;
-
Monitor the slow query log—the problematic query should disappear or show dramatically reduced execution time
-
Use
SHOW INDEX FROM ordersto see all indexes and their cardinality (number of unique values)
For production systems, deploy index changes during low-traffic periods. Creating indexes on large tables can lock the table for extended periods. MySQL supports online DDL for most index operations, but test in staging first.
Common MySQL Query Optimization Patterns
Beyond indexes, watch for these query patterns that often cause problems:
Functions on indexed columns: This prevents index use:
-- Bad: function prevents index use
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- Good: rewrite to allow index use
SELECT * FROM orders WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
SELECT * when you need few columns: Retrieve only necessary columns:
-- Better
SELECT order_id, customer_email, order_total FROM orders WHERE status = 'pending';
Inefficient JOINs: Always index foreign key columns used in joins:
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_product_id ON order_items(product_id);
Conclusion
MySQL slow query optimization follows a straightforward process: enable the slow query log to identify problems, use EXPLAIN to understand why queries are slow, and add targeted indexes to fix the bottlenecks. Start with the queries that appear most frequently in your slow query log or consume the most cumulative time—these give you the biggest performance wins.
Remember that database optimization is iterative. As your data grows and your application evolves, new slow queries will emerge. Make slow query log analysis a regular part of your maintenance routine, especially after deploying new features or experiencing traffic growth. A well-indexed database is the foundation of a responsive web application.