Fix Slow MySQL Queries: Log, EXPLAIN & Index Optimization

You hit refresh on a slow dashboard query and watch the seconds tick. The fix isn’t a hardware upgrade—it’s a combination of logging, analysis, and index tuning that MySQL already supports, yet the default long_query_time sits at 10 seconds (CoderPad (development blog)) with the slow query log switched off (Percona (MySQL consulting)), meaning many sluggish queries slip by unnoticed.

MySQL default long_query_time: 10 seconds ·
Slow query log status by default: disabled ·
EXPLAIN output rows examined: shows full table scan when no index ·
Query speed improvement with proper indexing: up to 100x ·
MySQL Performance Schema overhead: less than 1% CPU typically

Quick snapshot

1Confirmed facts

• Enabling slow query log captures queries exceeding long_query_time (Percona (MySQL consulting))
• EXPLAIN shows execution plan including rows scanned and possible keys (MySQL official documentation)
• Proper indexing can speed up queries up to 100× (Percona (MySQL performance))
• Performance Schema overhead typically less than 1% CPU (Severalnines (database operations))

2What’s unclear

• Exact performance impact of Performance Schema on high-write workloads is not well documented in benchmarks
• Whether WHERE IN with bind parameters uses indexes optimally across all MySQL versions

3Timeline signal

1. Enable slow query log and set threshold
2. Use EXPLAIN to analyze captured queries
3. Create or adjust indexes based on execution plan
4. Monitor with Performance Schema for continuous tuning

4What’s next

• Automate slow query analysis with pt-query-digest (Percona (MySQL consulting))
• Regularly update MySQL for optimizer improvements (GeeksforGeeks (tutorials))
• Combine Performance Schema with traditional logs for a complete picture

Five configuration details define where many MySQL speed searches begin.

Configuration	Value
Default slow_query_log state	OFF
Default long_query_time	10.000000 seconds
EXPLAIN type for full table scan	ALL
MAX possible key length for InnoDB index	767 bytes (or 3072 with large_prefix)
Performance Schema enabled by default since	MySQL 5.6.4

How to enable slow query logging in MySQL?

Setting the long_query_time threshold

• Set SET GLOBAL long_query_time = 2 to capture most slow queries (default is 10 seconds, CoderPad (development blog))
• For debugging, set to 0 to log every query temporarily
• Value is in seconds and supports fractional (e.g., 0.1)

Enabling the slow_query_log variable

• Run SET GLOBAL slow_query_log = 1 to switch on logging (GeeksforGeeks (tutorials))
• Must be placed in my.cnf for persistence: slow_query_log=ON
• File-based logging is default; performance impact is minimal

Choosing the log output destination

• Set log_output = FILE (default) or TABLE (writes to mysql.slow_log)
• File output is easier to rotate and parse with pt-query-digest
• Table output is convenient for SELECT queries on the log

The catch: Enabling the slow query log on a high-throughput production server may cause slight I/O pressure, but the value of catching a single runaway query far outweighs the overhead.

How to analyze slow queries using EXPLAIN?

Reading EXPLAIN output columns

• type – access method; ALL means full table scan (MySQL official documentation)
• rows – estimated rows examined; high numbers signal inefficiency
• Extra – often shows Using filesort or Using temporary as red flags

Identifying full table scans

• When type = ALL appears, MySQL is scanning every row – a clear sign that no index is being used (Percona (MySQL consulting))
• Check possible_keys and key: if possible_keys is non-NULL but key is NULL, index is not used

Detecting missing or unused indexes

• If key is NULL or different from possible_keys, the optimizer chose a different path
• Missing indexes on WHERE columns are the most common cause of slow SELECT queries (MySQL official documentation)
• Use SHOW INDEX FROM table to verify existing indexes

What this means: A single EXPLAIN quickly separates queries that need indexing from those that need rewriting. The pattern is consistent: if rows exceeds the table row count you expect, add an index.

How to optimize indexes to speed up MySQL queries?

Creating composite indexes for WHERE and JOIN

• Place the most selective column first (leftmost prefix rule) (Percona (MySQL performance))
• Composite indexes on (product_id, order_date) can accelerate many e‑commerce queries
• Columns used in WHERE should be indexed before ORDER BY columns

Avoiding over-indexing

• Too many indexes slow down INSERT/UPDATE and consume disk space
• Monitor index usage with performance_schema.table_io_waits_summary_by_index_usage
• Remove unused indexes after analysis

Using covering indexes

• When all selected columns are part of the index, Extra shows Using index – no table access needed (Percona (MySQL consulting))
• Covering indexes can eliminate disk I/O entirely, offering the biggest speed gains
• Design them for the most frequent read queries

The trade-off: Indexes speed reads but slow writes. For read-heavy applications the balance heavily favours indexing, but write-heavy tables require careful selectivity analysis.

How to fix common slow query patterns like WHERE IN and SELECT *?

Why WHERE IN can be slow with large lists

• MySQL may not use an index efficiently when the IN list contains hundreds or thousands of values (Percona (MySQL consulting))
• Consider breaking into batches of 100–200 IDs or using a temporary table
• Use EXISTS or JOIN instead of subqueries with IN

Rewriting SELECT * to select only needed columns

• SELECT * forces MySQL to read all columns, increasing I/O and network transfer (GeeksforGeeks (tutorials))
• Explicitly list the columns required – often 3–5 instead of 30
• Also helps covering indexes work because they don’t include unnecessary fields

Using EXISTS or JOIN instead of IN for subqueries

• MySQL often handles EXISTS and JOIN more efficiently than IN
• Rewrite WHERE id IN (SELECT id FROM orders WHERE ...) as JOIN orders ON ...
• Check the rewritten query with EXPLAIN – both should be tested

Why this matters: In PHP applications, many slow MySQL queries come from poorly constructed ORM calls that generate SELECT * or huge IN lists. Fixing these patterns often cuts query time from seconds to milliseconds without touching indexes.

How to use MySQL Performance Schema for query analysis?

Enabling Performance Schema

• Enabled by default since MySQL 5.6.4 (Severalnines (database operations))
• Can be toggled at startup with performance_schema=ON in my.cnf
• Overhead is minimal – typically less than 1% CPU

Querying events_statements_summary_by_digest

• This table aggregates queries by normalized digest, showing total wait time, count, and average latency (Severalnines (database operations))
• Times are in picoseconds – divide by 1e12 for seconds
• Sort by SUM_TIMER_WAIT to find the most expensive queries

Identifying high-load queries without log files

• Use sys.statement_analysis view (part of sys schema) for a simpler interface
• No need to enable slow query log for analysis – Performance Schema runs continuously
• Ideal for environments where log file management is constrained

The pattern: Performance Schema provides a real-time, zero‑maintenance alternative to the slow query log. For most teams, combining both gives the most complete picture: the log for history and schema for current load.

Clarity check

Confirmed facts

• Enabling slow query log allows capture of queries exceeding long_query_time
• EXPLAIN provides query execution plan
• Indexes reduce row scanning significantly

What’s still unclear

• Exact performance impact of Performance Schema on high-write workloads varies by workload type
• Whether WHERE IN with bind parameters uses indexes optimally across all MySQL versions depends on optimizer version

“The slow query log is the starting point for any tuning exercise. Without it, you’re guessing.”

— Percona (MySQL consulting), 2024

“Using EXPLAIN on a representative query tells you exactly where the optimizer is spending time. The output is often more valuable than guessing which index to add.”

— MySQL official documentation (select-optimization)

For developers and DBAs who invest an hour in this diagnostic workflow, the payoff is consistent. Queries that once forced page loads to crawl can be reduced from seconds to milliseconds – often with a single composite index or a query rewrite. The choice to ignore the slow query log or skip EXPLAIN means accepting that every user will wait a little longer. For a PHP application handling thousands of requests per minute, that latency accumulates into real revenue loss and user frustration.

For the team managing a MySQL‑backed service, the implication is clear: set long_query_time to 2 seconds, enable the slow query log, run EXPLAIN on the worst offenders, and build indexes that match the WHERE clauses. The alternative is a database that grows slower with every new feature.

Additional sources

percona.com, geeksforgeeks.org, percona.com, geraldvillorente.com, releem.com, coderpad.io, severalnines.com

Frequently asked questions

What is the default long_query_time in MySQL?

10 seconds. You can change it with SET GLOBAL long_query_time = <value> (CoderPad (development blog)).

How to set slow_query_log globally?

Execute SET GLOBAL slow_query_log = 1. For persistence, add slow_query_log=ON to my.cnf (GeeksforGeeks (tutorials)).

Does indexing always speed up queries?

No. Indexing speeds up SELECT queries but adds overhead to INSERT/UPDATE operations. Over‑indexing can degrade write performance and consume disk space.

What is the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN shows the estimated execution plan. EXPLAIN ANALYZE (MySQL 8.0.18+) runs the query and provides actual timings and rows examined (MySQL official documentation).

How to log queries slower than 1 second in MySQL?

Set SET GLOBAL long_query_time = 0.5 (or any value less than 1). Ensure slow_query_log is ON (Gerald Villorente Blog (community)).

Can Performance Schema cause performance degradation?

In most cases overhead is less than 1% CPU. On extremely high‑write workloads with many concurrent connections the overhead may be slightly higher, but it remains negligible for typical applications (Severalnines (database operations)).

What does ‘Using filesort’ mean in EXPLAIN output?

It means MySQL must sort the result set using an extra pass, often because no index covers the ORDER BY clause. Adding a composite index on the WHERE + ORDER BY columns can eliminate filesorting.

Related reading

• MacBook Air Ireland 2025: Prices, Models & Buying Guide
• Best E Ink Tablets 2026: Top Picks for Notes & Reading