How to Fix Slow MySQL Queries: Complete Step-by-Step Guide

Few things stall a late-night coding session like a MySQL query that simply won’t finish. This guide walks you through the exact steps to diagnose and resolve slow MySQL queries using the same tools that database administrators rely on every day.

Default slow query threshold: 10 seconds ·
Slow query log location: /var/lib/mysql/hostname-slow.log ·
Common cause: Missing indexes (90% of cases) ·
Max concurrent connections: 151

1. Enable the slow query log — Capture queries exceeding a threshold (e.g., 1 second) to find candidates for optimization.
2. Identify top offenders — Use mysqldumpslow or SHOW FULL PROCESSLIST to locate the slowest running queries.
3. Analyze execution plans — Run EXPLAIN on identified queries to spot full table scans or missing indexes.
4. Apply targeted indexes — Add or adjust indexes based on WHERE, JOIN, and ORDER BY columns.
5. Test and iterate — Re-run EXPLAIN and verify query time drops; repeat if needed.

Here is a quick reference table of key MySQL slow query facts.

How to make a MySQL query faster?

Use indexes effectively

• Indexing can speed up queries by 10x or more — Percona advises starting with the most selective column in composite indexes.
• For composite indexes, begin with the most selective column and end with the least selective one.
• Inspect existing indexes with SHOW INDEX FROM table; — the OneUptime guide highlights this as a first step.

Avoid SELECT *

SELECT * retrieves unnecessary columns and increases I/O. Name only the columns you need (MySQL Developer Zone).

Optimize JOINs and subqueries

• Use EXPLAIN to see execution plans.
• Ensure JOIN columns are indexed.
• Replace subqueries with JOINs where possible.

Limit result sets with LIMIT

Always use LIMIT when you only need a subset of rows. This reduces disk reads and network transfer.

Why does a MySQL query take so long to execute?

Missing or improper indexes

• Full table scans are slow on large tables. Without an index, MySQL must read every row (MySQL Developer Zone).
• Suboptimal WHERE clauses prevent index usage (e.g., using functions on indexed columns).

Large table scans

Even with indexes, queries that return many rows can be slow. Check the number of examined rows in EXPLAIN.

Lock contention

Row locks can block other queries. Use performance_schema.data_lock_waits to find waiting transactions (OneUptime). A query may appear slow because it’s waiting on a lock, not because it’s consuming CPU.

Inefficient query design

Multiple joins, missing ORDER BY indexes, and unoptimized sorting all contribute to latency. Always test queries under realistic data volumes.

The implication: start each diagnosis by checking the execution plan before diving into configuration tuning.

How to get long running queries in MySQL?

Enable slow query log

• The slow query log records statements that exceed long_query_time (MySQL Developer Zone).
• Set it dynamically: SET GLOBAL slow_query_log = ON; and SET GLOBAL long_query_time = 1; (OneUptime shows this runtime setting).
• For permanent configuration, add settings under [mysqld] in my.cnf (Hostek Community).

Use SHOW FULL PROCESSLIST

SHOW FULL PROCESSLIST shows all currently running queries. Identify long-running ones by their Time column — OneUptime recommends this for immediate visibility.

Query performance_schema

performance_schema provides detailed metrics on statement execution, lock waits, and IO (MySQL Developer Zone).

Use mysqldumpslow to summarize logs

• mysqldumpslow -s at -t 10 lists the top 10 slowest queries by average time — this command appears in the OneUptime guide.
• Aggregate logs from multiple servers for a system-wide view.

“The slow query log can be used to find queries that take a long time to execute and are therefore candidates for optimization.”

— MySQL Documentation, MySQL Developer Zone (official product docs)

The pattern: detection via logging is the necessary first step before any analysis begins.

How to fix slow mysql queries in php?

Use prepared statements

Prepared statements improve performance on repeated queries by allowing MySQL to cache the execution plan (MySQL Developer Zone).

Limit data with pagination

Use LIMIT and OFFSET to paginate large result sets. This reduces memory usage and query time.

Cache results with memcached or Redis

Caching offloads repeated read queries from the database. Even a TTL of 60 seconds can dramatically reduce load.

Profile PHP MySQL calls

• Use slow_query_log to capture queries originating from PHP.
• Instrument your PHP code to log query execution times.

“Percona recommends a general workflow of finding the slow query, checking the EXAPLAN, reviewing the table definition, creating indexes, and then rechecking the updated plan.”

— Percona (MySQL performance consultants)

The takeaway: combining prepared statements with pagination and caching keeps PHP applications responsive even under heavy load.

How to fix slow mysql queries mac?

Check MySQL configuration on macOS

The default Homebrew MySQL config (my.cnf) may not be tuned for your hardware. Locate it at /usr/local/etc/my.cnf (Intel) or /opt/homebrew/etc/my.cnf (Apple Silicon).

Use Homebrew MySQL settings

Edit the configuration file to increase key buffers and thread cache. Start with small changes and test over several days (Hostek Community).

Increase innodb_buffer_pool_size

Set innodb_buffer_pool_size to about 70% of available RAM. On a Mac with 16 GB, that’s roughly 11 GB. This keeps more data in memory and reduces disk reads.

Monitor with Activity Monitor

Use macOS Activity Monitor to check mysqld CPU and memory usage. If it pegs a core, the query is likely CPU-bound.

The consequence of ignoring hardware-specific tuning: even well-indexed queries can underperform on macOS due to suboptimal defaults.

The pattern is clear: detection comes first, then analysis with EXPLAIN, then targeted indexing. Skipping any step leaves you guessing. For the developer with a slow query, the fix is often a single well‑placed index.

Frequently asked questions