MySQL Performance Tuning Guide

By Kokil Thapa | Last reviewed: September 2026

A slow database kills every layer above it. No amount of front-end polish fixes a 3-second query on checkout or a booking form that locks under load. This MySQL Performance Tuning Guide walks through the workflow I use on production Laravel, WooCommerce, and custom PHP apps backed by MySQL performance tuning for web applications. You will measure first, fix queries and indexes second, and only then touch server variables. That order prevents the classic mistake of raising max_connections while a full table scan still runs on every page view.

What Is the First Step in a MySQL Performance Tuning Guide?

Start with a baseline. Capture what is slow, how often it runs, and under what load. Without numbers, you cannot prove a fix worked or spot regressions after the next deploy.

On Ubuntu servers I maintain, I pull four signals before changing anything:

1. Application timing — Laravel Debugbar in staging, or APM in production
2. MySQL slow query log — queries above a sensible threshold
3. SHOW GLOBAL STATUS — buffer pool hit rate, temp tables, sort merges
4. OS metrics — CPU, disk I/O wait, swap usage

Enable the slow query log in /etc/mysql/mysql.conf.d/mysqld.cnf (path varies by distro):

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

Restart or reload MySQL, reproduce traffic, then inspect with mysqldumpslow or MySQL Performance Schema. A threshold of 1 second works for most web apps. APIs may need 0.5 seconds. Batch jobs can use 5 seconds to reduce noise.

Store baseline numbers in a spreadsheet or ticket. Page load, p95 query time, and buffer pool hit ratio are enough for most teams. If you cannot replay traffic, schedule tuning during a low-traffic window and watch error rates closely.

How Do You Find Slow MySQL Queries in Production?

The slow query log tells you what hurt users. Performance Schema tells you why. Use both when possible.

Reading EXPLAIN output

Every tuning session eventually lands on EXPLAIN. Run it on the exact SQL your app sends — not a simplified version:

EXPLAIN ANALYZE
SELECT o.id, o.total, c.name
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;

Watch for these red flags:

• type = ALL — full table scan on a large table
• rows in the millions for a paginated list
• Using filesort or Using temporary on high-traffic paths
• key = NULL when a filter column clearly needs an index

For deeper analysis, enable the performance schema and query events_statements_summary_by_digest. That surfaces queries by total time, not just individual slow executions. On a legal-tech portal I built, one digest accounted for 40% of database time. It was a report query missing a composite index — not the checkout path we assumed was the problem.

pt-query-digest and Laravel Telescope

Percona Toolkit's pt-query-digest groups slow log entries by fingerprint. In Laravel apps, pair it with Laravel performance optimization techniques and watch for N+1 patterns in Eloquent. A single controller action firing 80 queries often beats one slow query for total damage.

See also MySQL query optimization for slow queries and optimizing MySQL queries for high-traffic applications for worked examples on pagination and JOIN order.

How Should You Design Indexes for MySQL Performance?

Indexes are the highest-ROI fix in most tuning jobs. They are also the easiest way to slow writes and waste disk if applied blindly.

Composite index column order

MySQL uses a composite index left-to-right. For a query filtering by status and sorting by created_at, this index usually wins:

ALTER TABLE orders
ADD INDEX idx_orders_status_created (status, created_at);

Put equality columns first. Range columns last. If you filter status = 'pending' and created_at >= '2026-01-01', the same index still works — but only if status is the leading column.

Covering indexes and selective filters

A covering index includes all columns the query reads. MySQL can satisfy the query from the index alone:

ALTER TABLE orders
ADD INDEX idx_orders_list_covering (status, created_at, id, total);

Do not index every column. Low-cardinality fields like boolean flags rarely help alone. Combine them with selective columns or skip them. Read MySQL index design deep dive and database indexing for performance before adding five indexes to one table.

On the Quick And Easy Nepalese Grocery Laravel store, delivery-zone lookups needed a composite index on (zone_id, is_active). Without it, zone checks scanned the full product table on every cart update.

Which MySQL Server Variables Matter Most for Tuning?

Server tuning comes after query fixes. MySQL 9.7 and the 8.4 LTS line share the same InnoDB core. Most shared hosts lock these variables — self-managed Ubuntu boxes do not.

InnoDB buffer pool

The buffer pool caches data and index pages in RAM. It is the single most important memory setting:

-- Check current size (bytes)
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Target 60-70% of dedicated DB RAM on a DB-only server
SET GLOBAL innodb_buffer_pool_size = 4294967296;  -- 4 GB

Check hit rate after a warm-up period:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Calculate: 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). Aim above 99% on steady-state production. Below 95% usually means the pool is too small or the working set grew.

Connections, temp tables, and I/O

Match max_connections to your PHP-FPM or app pool size plus admin headroom. Setting 500 connections on a 2 GB RAM box creates thrashing, not capacity. Tune PHP-FPM pools in parallel — see PHP-FPM tuning for high-traffic websites.

Other variables worth reviewing:

• innodb_log_file_size — larger logs reduce flush pressure on write-heavy tables
• tmp_table_size / max_heap_table_size — raise together if EXPLAIN shows memory temp tables spilling to disk
• innodb_flush_log_at_trx_commit — value 2 trades durability for speed on non-financial logs (know the risk)
• table_open_cache — increase when you see "Opening tables" waits on schema-heavy apps

For large historical tables, consider MySQL partitioning for large tables before throwing RAM at full scans. Partition pruning helps time-range reports on audit and order history.

Fresh installs on Ubuntu should follow install MySQL on Ubuntu and LEMP stack setup before custom tuning. Default configs assume a small dev machine, not a production web app.

How Do You Tune MySQL for Laravel and PHP Web Applications?

Application-layer fixes often beat server knobs. On Laravel 12 or 13.x with PHP 8.3+, these patterns recur across client projects.

Eliminate N+1 and lazy loading

// Bad — N+1 on order lines
$orders = Order::where('status', 'pending')->get();
foreach ($orders as $order) {
    echo $order->customer->name;
}

// Good — eager load
$orders = Order::with('customer')
    ->where('status', 'pending')
    ->latest()
    ->limit(50)
    ->get();

Use ->select() to fetch only needed columns on large tables. Avoid SELECT * on tables with JSON blobs or long text fields.

Caching with Redis

Redis 8.10 sitting in front of MySQL handles session data, rate limits, and expensive aggregates. Cache invalidation must be explicit — stale inventory counts cause real eCommerce losses. Read improving web performance with caching strategies for TTL and tag patterns that work with Laravel's cache driver.

Do not cache everything. User-specific dashboards and legal document lists often need fresh reads. Product category trees and CMS menus are safe cache candidates.

Schema and migration hygiene

Use appropriate types. BIGINT for high-volume IDs. DECIMAL(12,2) for money — never float. Add foreign keys where referential integrity matters; InnoDB indexes FK columns automatically on the child side.

Run ANALYZE TABLE orders; after large imports so the optimizer picks correct indexes. On Deployer 7 releases I maintain, post-deploy migrations on big tables run through pt-online-schema-change or native ALGORITHM=INPLACE to avoid full locks.

Managed RDS versus self-hosted EC2 is a cost and ops trade-off, not a performance silver bullet. See RDS vs self-managed MySQL on EC2 for when each makes sense. For teams comparing engines, PostgreSQL vs MySQL for production and MariaDB vs MySQL in 2026 cover migration triggers — not every slowdown means switch databases.

Replication and binary logs matter for read scaling and backups. Plan those after single-server tuning stabilizes — MySQL binary logs for replication and backup covers retention without filling disk.

Professional audits combine database work with server review. Our testing and optimization service and Linux system administration cover the full stack. Ongoing fixes land under support and maintenance when clients need monthly monitoring.

Use the JSON formatter tool when inspecting API payloads that trigger heavy JSON column queries. MySQL JSON functions are convenient but easy to abuse — functional indexes help, but simpler schemas often win.

Key Takeaways

• Enable the slow query log and capture a baseline before changing indexes or config.
• Run EXPLAIN ANALYZE on exact production SQL — fix scans and filesorts first.
• Build composite indexes with equality columns leading, then range columns.
• Set innodb_buffer_pool_size to 60–70% of dedicated DB RAM after query fixes.
• Eliminate Laravel N+1 queries and cache read-mostly data in Redis 8.10 with clear TTLs.
• Re-measure under the same workload — tuning without verification is guesswork.

People Also Ask

What is the most common cause of slow MySQL performance?

Missing or wrong indexes on hot queries cause most web app slowdowns. Full table scans on growing tables, N+1 ORM patterns, and SELECT * on wide rows follow close behind. Server misconfiguration ranks lower until query paths are clean.

How big should the InnoDB buffer pool be?

On a dedicated database server, allocate 60–70% of RAM to innodb_buffer_pool_size. On shared app+DB boxes, split carefully — starving PHP-FPM or the OS page cache creates new bottlenecks. Monitor hit rate and adjust.

Is MySQL 9.7 better for performance than MySQL 8.4 LTS?

MySQL 9.7 adds optimizer improvements and newer features. MySQL 8.4 LTS remains the safer choice on managed hosting with long support windows. Performance gains come mainly from indexes, query design, and hardware — not the minor version alone.

When should you partition a MySQL table?

Partition when a table exceeds tens of millions of rows and queries consistently filter by a partition key like created_at. Partitioning without matching query patterns adds complexity without speed gains. Archive old data when legal retention allows.

Ship Faster Queries With a Structured Tuning Plan

A repeatable MySQL Performance Tuning Guide beats one-off heroics every release cycle. Measure slow queries, fix indexes and application patterns, tune InnoDB memory, then verify under real traffic. That sequence has cleared production bottlenecks on eCommerce, booking, and legal-tech apps I have shipped since 2010 — without unnecessary database migrations or hardware spend.

If your app still stalls under load after internal fixes, an external review often finds the one digest eating half your database time. Contact us for a performance audit, or browse the portfolio for Laravel and MySQL projects already running in production. For related reading, start with MySQL optimization for SaaS and the master-slave replication setup guide when you outgrow a single server.