MySQL Query Optimization (EXPLAIN, Slow Query Log, Indexes)

MySQL Query Optimization (EXPLAIN, Slow Query Log, Indexes)

We deal with databases where 80% of slow queries are caused by three reasons: missing indexes, incorrect index usage, or non-optimal JOINs. Slow Query Log is the first tool to find these issues. It can be enabled with two lines in my.cnf, but you need to know how to read it—and what to do with what you find. Over 5+ years, we have analyzed hundreds of projects and guarantee query speedups of up to 10x. For example, in one project, a SELECT with JOIN took 12 seconds—after adding a covering index, the time dropped to 0.03 seconds, a 400x improvement. This reduction in response time lowered server load and saved significant infrastructure costs. Our team has 5+ years of MySQL optimization experience and has audited over 200 databases.

Slow Query Log Configuration and Analysis

slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
log_throttle_queries_not_using_indexes = 10
min_examined_row_limit = 1000

The raw log is cumbersome. For aggregation, use pt-query-digest from Percona Toolkit—it groups identical queries and shows statistics: number of executions, total and average time, ratio of rows_examined to rows_sent. A high rows_examined with low rows_sent indicates a poor or missing index.

For online monitoring without a file-based log, query performance_schema.events_statements_summary_by_digest:

SELECT ROUND(SUM_TIMER_WAIT/1e12, 2) AS total_sec,
       COUNT_STAR AS calls,
       ROUND(AVG_TIMER_WAIT/1e12, 4) AS avg_sec,
       ROUND(SUM_ROWS_EXAMINED/COUNT_STAR) AS avg_rows_examined,
       LEFT(DIGEST_TEXT, 80) AS query
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'mydb'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

How to Read EXPLAIN: Query Execution Plan

EXPLAIN FORMAT=JSON
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.status = 'active'
GROUP BY u.id;

FORMAT=JSON gives a full plan with cost estimates. For quick reading, use the plain EXPLAIN. What to check first: type column — access method to the table. Hierarchy from best to worst:

ALL on a large table in the middle of a JOIN is an immediate red flag. A covering index is 100x more efficient than a full scan.

rows — estimated number of rows MySQL expects to examine. If the plan has 3 tables with rows=500, 5000, 1000—potentially 2.5 billion combinations in a nested loop without indexes.

Extra — additional operations:

• Using filesort — sorting outside the index, in memory or on disk
• Using temporary — temporary table (GROUP BY, DISTINCT without index)
• Using index — covering index (good)
• Using index condition — index condition pushdown (good)

We use these metrics for automated analysis: scripts extract problematic queries and suggest ready-to-use indexes. Contact us to get such analysis for your database.

Creating Indexes for Specific Queries

When we see type=ALL on orders for a JOIN on user_id:

CREATE INDEX idx_orders_user_id ON orders(user_id); 

Column order in composite indexes is critical. The leftmost prefix rule: index (status, created_at) works for WHERE status = ?, for WHERE status = ? AND created_at > ?, but not for WHERE created_at > ? without status.

Covering index—the query gets data directly from the index without accessing table rows:

CREATE INDEX idx_orders_covering ON orders(user_id, status, total); 

EXPLAIN will show Extra: Using index—data from index, no heap lookup.

Common Slow Query Patterns

LIKE with leading wildcard. WHERE name LIKE '%pattern%' — index not used, always full scan. Solution: FULLTEXT index or Elasticsearch for full-text search.

Function on indexed column. WHERE YEAR(created_at) = 2021 — index on created_at is ignored. Rewrite as WHERE created_at >= '2021-01-01' AND created_at < '2022-01-01'.

OR on different columns. WHERE status = 'active' OR priority = 'high' — MySQL may use index merge, but often worse than two separate queries with UNION ALL.

Large OFFSET in pagination. LIMIT 20 OFFSET 50000 — MySQL reads 50020 rows and discards 50000. Solution: keyset pagination: WHERE id > :last_id ORDER BY id LIMIT 20.

How to Automate Slow Query Monitoring

We recommend setting up slow log collection via Prometheus exporter or using a real-time analysis plugin. In our practice, Python scripts analyze performance_schema and send alerts when thresholds are exceeded (e.g., avg_sec > 0.1). This allows reacting before user complaints.

Optimization Process and What’s Included

1. Collect top slow queries via pt-query-digest or performance_schema.
2. EXPLAIN each query, look for type=ALL, Using filesort, Using temporary.
3. Create indexes using CREATE INDEX (non-blocking in MySQL 5.6+ for InnoDB).
4. Recheck plans with EXPLAIN and test on production data.
5. Provide a report with results and monitoring recommendations.

Timeline for point optimization of 5-10 queries: 1-2 days. System audit with analysis of all slow queries, index overhaul, and schema optimization: 2-3 days. Cost is determined after preliminary analysis.

Why Choose Us?

With 5+ years, we have audited over 200 databases, speeding up critical queries by an average of 15x. We use proven tools: Percona Toolkit and official MySQL EXPLAIN documentation.

Get a consultation on your database optimization—order an audit and speed up queries up to 10x. Contact us to discuss your project. The savings from optimization can be significant due to reduced server load.