MySQL Query Optimization for Slow Queries

By Kokil Thapa | Last reviewed: September 2026

A page that worked at launch can crawl once order volume grows. MySQL query optimization for slow queries is the disciplined process of finding statements that exceed your latency budget, reading their execution plans, and fixing root causes—usually missing indexes, bad joins, or ORM patterns that scan millions of rows. On production Laravel and WooCommerce systems I maintain, slow queries rarely announce themselves until cron jobs overlap peak traffic. This guide walks through the same workflow I use: measure first, explain second, change schema or SQL third, tune the server last. For broader context, see our guide on optimizing MySQL queries for high-traffic applications.

How do you find slow MySQL queries in production?

You cannot optimize what you do not measure. Start with data, not assumptions.

MySQL 9.7 and the widely deployed 8.4 LTS line both expose slow-query instrumentation through the slow query log and Performance Schema. On Ubuntu servers I administer, I enable the slow log with a threshold tied to real user experience—not the default ten seconds.

Enable the slow query log

Add these settings to my.cnf or a drop-in under /etc/mysql/mysql.conf.d/:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 0

A long_query_time of one second catches admin reports and checkout flows that feel broken. Tighten it to 0.5 during investigation windows. Set log_queries_not_using_indexes to 0 in production unless you enjoy log floods.

Reload MySQL after config changes:

sudo systemctl reload mysql

Use Performance Schema for live analysis

The slow log is historical. Performance Schema shows what is slow right now. This query surfaces the worst offenders by total wait time:

SELECT
  DIGEST_TEXT AS query_pattern,
  COUNT_STAR AS exec_count,
  ROUND(AVG_TIMER_WAIT / 1e12, 3) AS avg_sec,
  ROUND(SUM_TIMER_WAIT / 1e12, 2) AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

Official reference: MySQL Performance Schema statement summary tables.

Laravel teams should also enable query logging in staging and read Laravel N+1 query detection and fixes. A single controller action can fire fifty identical lookups. That pattern never appears as one slow row in the slow log.

Application-level APM and tools like Laravel Debugbar complement server logs. They show which Eloquent relationship triggered the scan. On a booking portal I built with Livewire, the culprit was an unbounded with() on a date-range filter—not the SQL text itself.

Parse slow log output with pt-query-digest

Percona Toolkit's pt-query-digest groups similar statements and ranks them by total impact:

pt-query-digest /var/log/mysql/slow.log > /tmp/digest.txt

Focus on queries with high Query_time sums, not isolated spikes. One slow admin export matters less than a checkout lookup running ten thousand times per hour.

How does EXPLAIN help with MySQL query optimization for slow queries?

EXPLAIN is the X-ray. It shows which indexes MySQL chose and where it gave up.

Run it on the exact SQL your application sends—including bound parameters in a realistic test dataset. Plans change with row counts.

Read the columns that matter

Prioritize these EXPLAIN output fields:

• type — ALL means full scan; aim for ref, range, or const.
• key — which index was used; NULL is a red flag on large tables.
• rows — estimated rows examined; compare before and after fixes.
• Extra — watch for Using filesort, Using temporary, Using where.

On MySQL 8.4+ and 9.7, prefer EXPLAIN ANALYZE for actual timings:

EXPLAIN ANALYZE
SELECT o.id, o.total, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

Official syntax reference: MySQL EXPLAIN statement documentation.

Cross-check plans after schema changes. I've seen deployments where a new composite index helped one report but forced a worse plan on nightly batch jobs. Always EXPLAIN both paths.

For deep index theory, read our MySQL index design deep dive. For ORM-specific patterns, see Laravel Eloquent advanced query patterns for large datasets.

What indexes fix the most common slow MySQL query patterns?

Indexes are not free. Each one slows writes and consumes disk. Add them with intent.

Match indexes to your WHERE, JOIN, and ORDER BY columns—in that selectivity order. A composite index on (status, created_at) supports filtering by status and sorting by date within that subset.

Composite index column order

Put the most selective leading column first when equality filters precede ranges:

CREATE INDEX idx_orders_status_created
ON orders (status, created_at);

This index serves WHERE status = 'pending' ORDER BY created_at DESC. It does not help a query that filters only on created_at without status. That requires a separate index or a query rewrite.

Covering indexes for hot reads

A covering index includes all columns the SELECT needs. MySQL reads only the index tree—no table lookup:

CREATE INDEX idx_orders_list_cover
ON orders (status, created_at, id, total);

Use covering indexes sparingly on wide tables. They bloat quickly. On read-heavy catalog pages in WooCommerce 11.1 stores, a slim covering index on (post_status, menu_order, ID) often beats caching stale product lists.

Foreign keys and join columns

Every JOIN column on the driving side needs an index. Missing join indexes are the fastest way to turn a 50 ms query into a 30-second one:

CREATE INDEX idx_order_items_order_id ON order_items (order_id);

Our Laravel query optimization for 1M-row tables article walks through real Eloquent equivalents of these patterns.

Index maintenance checklist

1. Run SHOW INDEX FROM table_name before adding duplicates.
2. Drop unused indexes found via Performance Schema or sys.schema_unused_indexes.
3. Rebuild stats after large imports: ANALYZE TABLE orders;
4. Test write throughput on hot insert tables before adding wide composites.

SaaS workloads with tenant isolation often need leading tenant_id columns. Our MySQL optimization for SaaS indexing and partitioning covers multi-tenant edge cases.

When should you rewrite a query instead of adding another index?

Indexes have diminishing returns. Sometimes the SQL or schema shape is wrong.

Replace SELECT * with explicit columns

Wide rows force more I/O and prevent covering indexes from helping. Select only what the view or API response needs:

SELECT id, total, status, created_at
FROM orders
WHERE customer_id = 8842
ORDER BY created_at DESC
LIMIT 20;

Fix OR and function-wrapped columns

WHERE YEAR(created_at) = 2026 prevents index use on created_at. Rewrite to a range:

WHERE created_at >= '2026-01-01'
  AND created_at < '2027-01-01'

OR conditions across different columns often need UNION ALL instead of one mega-index:

SELECT id, total FROM orders WHERE status = 'pending'
UNION ALL
SELECT id, total FROM orders WHERE status = 'processing' AND priority = 1;

Eliminate N+1 and pagination offsets

Offset pagination degrades linearly. LIMIT 100000, 50 scans and discards 100,000 rows:

SELECT id, created_at, total
FROM orders
WHERE created_at < '2026-03-15 14:22:00'
ORDER BY created_at DESC
LIMIT 50;

Keyset pagination uses the last seen value as a cursor. It stays fast as tables grow. Pair it with Redis 8.10 caching for frequently accessed first pages—see database query caching strategies.

On the Quick And Easy Nepalese Grocery Laravel store, delivery-zone lookups slowed after catalog growth. Rewriting a subquery to a JOIN with a zone index cut admin load time more than adding a third redundant index.

How do you tune MySQL server settings for slow workloads?

Server tuning comes after query and index fixes. Tuning a bad query with more RAM only delays the pain.

Still, buffer pool and connection settings matter on busy hosts. I follow patterns from our MySQL performance tuning for web applications guide and validate on staging clones.

InnoDB buffer pool

Set innodb_buffer_pool_size to roughly 60–70% of dedicated DB RAM on a single-purpose server. On a 8 GB database VM, start near 5 GB:

innodb_buffer_pool_size = 5G
innodb_buffer_pool_instances = 4

Watch buffer pool hit rate:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

If Innodb_buffer_pool_reads stays high relative to requests, you need more RAM or fewer cold scans—not arbitrary query cache hacks.

Join and sort buffers

Raise these modestly when EXPLAIN shows large joins or sorts—but only after indexing:

join_buffer_size = 256K
sort_buffer_size = 512K
read_rnd_buffer_size = 256K

Oversized sort buffers per connection multiply memory use under concurrency. A site with 200 PHP-FPM workers can exhaust RAM quickly.

Connection and timeout hygiene

• Set max_connections above peak pool size with headroom—not unlimited.
• Use wait_timeout to drop idle connections from misconfigured apps.
• Enable slow_query_log rotation so disks do not fill during incidents.
• Schedule mysqlcheck and verify backups via MySQL binary logs for replication and backup.

If you run MariaDB 12.3 instead of Oracle MySQL, most EXPLAIN and index advice transfers—but verify optimizer differences in our MariaDB vs MySQL comparison for 2026. Considering a move to PostgreSQL 18? Read PostgreSQL vs MySQL for production before optimizing a database you plan to leave.

Fresh installs benefit from a clean baseline—follow install MySQL on Ubuntu before layering production tuning. WordPress-heavy stacks should also audit plugin queries via WordPress database optimization for large sites.

Key Takeaways

• Enable the slow query log and Performance Schema before changing schema—measure total wait time, not single spikes.
• Run EXPLAIN ANALYZE on exact production SQL; fix type: ALL and Using filesort before raising buffer pool size.
• Build composite indexes with equality columns first, then range and ORDER BY columns; drop unused indexes that slow writes.
• Rewrite non-sargable predicates, OFFSET pagination, and ORM N+1 loops—indexes alone cannot fix application query shapes.
• Validate every change on a staging clone with realistic row counts; plans flip when tables cross million-row thresholds.
• Pair database work with app caching and professional testing and optimization services when slowdowns block revenue paths.

People Also Ask

What is a good long_query_time threshold?

One second is a practical production default for user-facing web apps. Drop to 0.2–0.5 seconds during active investigations. Batch ETL jobs may legitimately run longer—tag or route them to separate instances so they do not drown OLTP signals.

Does adding more indexes always speed up MySQL?

No. Each index slows INSERT, UPDATE, and DELETE operations and consumes disk. Duplicate or unused indexes confuse the optimizer and increase write amplification. Audit with Performance Schema and remove indexes that never appear in EXPLAIN output.

How do Laravel and WordPress developers find slow queries?

Laravel: enable query logging, use Debugbar in staging, and fix N+1 with eager loading. WordPress: Query Monitor plugin surfaces duplicate and slow queries per page load. Both still need MySQL EXPLAIN on the underlying SQL—not just application timers.

When should you upgrade hardware instead of optimizing queries?

Upgrade when buffer pool hit rate stays poor after index and rewrite work, disk I/O saturates on legitimate indexed reads, or replication lag persists despite clean SQL. Throwing RAM at full scans buys weeks, not years.

Ship faster queries with a clear optimization plan

MySQL query optimization for slow queries is repeatable engineering—not guesswork. Log slow statements, EXPLAIN the top offenders by total time, fix indexes and SQL shapes, then tune InnoDB and caching. That sequence has stabilized checkout flows, booking calendars, and legal-tech search on systems I operate under real Nepal and international traffic.

If your team lacks time to profile production safely, start with a focused audit through our speed optimization service or broader web development support. For JSON-heavy debugging of API payloads during query refactors, the JSON formatter tool on this site saves friction. Need hands-on help? Contact us with your slow query log excerpt and table sizes—we will map a fix path before anyone adds a random index.