Last updated: 17 May 2026
MySQL performance tuning involves five core areas: index optimisation, query rewriting, buffer pool sizing, connection pooling and slow query log analysis. A properly tuned MySQL instance handles 3-10x more concurrent connections and reduces average query time by 60-80%. Most UK business MySQL databases have at least 3 indexing mistakes causing unnecessary full table scans.
In database audits across UK businesses of all sizes, the same five problems appear repeatedly. None of them require hardware upgrades. All five are fixable in under a day once correctly diagnosed.
| Problem | Cause | Fix | Typical Impact |
|---|---|---|---|
| Full table scans on large tables | Missing index on filtered column | Add composite index on WHERE clause columns | 10-1,000x query speed improvement |
| Default 128MB buffer pool on multi-GB server | Installation default never changed | Set innodb_buffer_pool_size to 60-80% of RAM | 50-80% reduction in disk I/O |
| N+1 query patterns in Laravel/PHP | Missing eager loading in ORM code | Add with() or join() to ORM queries | 100-500 fewer queries per page load |
| SELECT * on large tables | Developer convenience over performance | Specify required columns explicitly | Enables covering indexes, reduces I/O by 30-70% |
| No slow query logging | Default MySQL install has it disabled | Enable slow_query_log in my.cnf | Identifies the specific queries causing problems |
The table above represents the starting point for every MySQL performance audit. Before changing any index or configuration setting, enable slow query logging and identify which queries are actually causing the degradation. Tuning queries that are not on the slow query log is wasted effort.
A key point about MySQL on shared hosting or basic VPS environments: the default MySQL installation is configured for minimal memory footprint, not performance. The default innodb_buffer_pool_size of 128MB was appropriate for servers with 256MB of RAM. On a server with 8GB or 32GB of RAM, that same default leaves 95-99% of available memory unused for database caching. This is the single most common correctable MySQL performance mistake in UK SME deployments.
MySQL uses B-tree indexes for most column types. Understanding how MySQL selects indexes -- and when it ignores them -- determines whether your indexes actually improve query performance or simply add write overhead.
Composite indexes and the leftmost prefix rule:
A composite index on (customer_id, order_date, status) can be used by queries that filter on customer_id alone, customer_id AND order_date, or all three columns together. It cannot be used by queries that filter on order_date alone or status alone. This is the leftmost prefix rule, and violating it is the most common composite index mistake.
-- Create a composite index for common query patterns
CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);
-- This query uses the index (leftmost prefix: customer_id)
SELECT order_id, total FROM orders WHERE customer_id = 12345;
-- This query also uses the index (both columns)
SELECT order_id, total FROM orders
WHERE customer_id = 12345 AND order_date >= '2026-01-01';
-- This query does NOT use the index (skips leftmost column)
SELECT order_id, total FROM orders WHERE order_date >= '2026-01-01';
-- Full table scan -- needs a separate index on order_date
Covering indexes:
A covering index contains all columns referenced in a query -- both filter columns and selected columns. When MySQL can answer a query entirely from the index without touching the main table, it uses an index-only scan (shown as Using index in EXPLAIN Extra column). This eliminates the most expensive part of query execution: the random I/O reads to fetch data from the main table pages.
-- Query that benefits from a covering index
SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345 AND order_date >= '2026-01-01';
-- Non-covering index: MySQL must look up the table for total_amount
CREATE INDEX idx_orders_no_cover ON orders (customer_id, order_date);
-- Covering index: total_amount included, no table lookup needed
-- MySQL does not support INCLUDE syntax; add the column to the key instead
CREATE INDEX idx_orders_covering ON orders (customer_id, order_date, total_amount);
-- Verify coverage with EXPLAIN
EXPLAIN SELECT customer_id, order_date, total_amount
FROM orders
WHERE customer_id = 12345;
-- Extra column should show: Using index (covering)
-- NOT: Using index condition (still requires table lookup)
Index cardinality and when indexes are ignored:
MySQL's query optimiser estimates the cost of using each available index and may choose a full table scan if it estimates that the index selectivity is too low. You can check index cardinality with:
SHOW INDEX FROM orders;
-- Cardinality column shows estimated distinct values
-- Low cardinality on a large table = MySQL may ignore the index
-- Force MySQL to update statistics (run after large data loads)
ANALYZE TABLE orders;
-- Check if MySQL is using your index
EXPLAIN SELECT * FROM orders WHERE customer_id = 12345;
-- key column shows which index was chosen
-- key = NULL means no index used (full table scan)
-- type = ALL means full table scan regardless of indexes
Reading EXPLAIN output:
The type column in EXPLAIN output is the most important indicator of query efficiency, from best to worst: system (single row) > const (single row by primary key) > eq_ref (one row per join) > ref (index lookup, multiple rows) > range (index range scan) > index (full index scan) > ALL (full table scan). For tables over 10,000 rows, ALL or index in the type column indicates a missing or unusable index that needs investigation.
The InnoDB buffer pool is MySQL's primary memory cache. It stores data pages and index pages in memory so that frequently accessed data can be served from RAM rather than disk. Correct buffer pool sizing is the most impactful single server configuration change for MySQL performance.
Setting innodb_buffer_pool_size by server RAM:
| Server RAM | Recommended innodb_buffer_pool_size | innodb_buffer_pool_instances | Notes |
|---|---|---|---|
| 2GB | 1.2G | 1 | Minimal; single instance fine |
| 4GB | 2.5G | 2 | Leave 1.5GB for OS and connections |
| 8GB | 5G | 4 | Standard small UK VPS |
| 16GB | 10G | 8 | Common dedicated server |
| 32GB | 22G | 8 | High-traffic application server |
| 64GB | 48G | 16 | Dedicated DB server, UK hosting |
Applying the configuration (my.cnf or /etc/mysql/mysql.conf.d/mysqld.cnf):
[mysqld]
# Buffer pool -- most important MySQL setting
# Set to 60-80% of total server RAM on dedicated DB servers
# Set to 40-60% if MySQL shares server with PHP/application
innodb_buffer_pool_size = 10G
# Use multiple instances for buffer pools > 1GB
# Reduces mutex contention under high concurrency
innodb_buffer_pool_instances = 8
# InnoDB redo log size -- larger = fewer checkpoints = better write throughput
# MySQL 8.0+: innodb_redo_log_capacity replaces innodb_log_file_size
innodb_redo_log_capacity = 2G # MySQL 8.0.30+
# Or for older MySQL 8.0:
# innodb_log_file_size = 1G
# Flush method for Linux -- O_DIRECT avoids double buffering with OS cache
innodb_flush_method = O_DIRECT
# Commit durability vs performance tradeoff
# 1 = fully ACID compliant (use for financial data)
# 2 = syncs log buffer every second (1 second max data loss, faster writes)
innodb_flush_log_at_trx_commit = 1
Verifying buffer pool utilisation:
After restarting MySQL with new settings, verify the buffer pool is being used effectively:
-- Check buffer pool hit rate (should be > 99% after warmup)
SELECT
FORMAT(((Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads)
/ Innodb_buffer_pool_read_requests) * 100, 2) AS buffer_pool_hit_rate
FROM (
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_read_requests
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
) AS rr,
(
SELECT
VARIABLE_VALUE AS Innodb_buffer_pool_reads
FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
) AS r;
-- Alternative: SHOW STATUS for quick check
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- Innodb_buffer_pool_read_requests: total page reads attempted
-- Innodb_buffer_pool_reads: reads that went to disk (cache miss)
-- Hit rate = (read_requests - reads) / read_requests
-- Target: > 99%
A hit rate below 95% after the server has warmed up (typically 30-60 minutes after restart with normal traffic) suggests the buffer pool is undersized for your working data set. Either increase the buffer pool or, if you cannot allocate more memory to MySQL, investigate whether an oversized history or log table is consuming buffer pool space that active application data needs.
Additional InnoDB performance settings:
[mysqld]
# Read I/O threads for background reads (increase for SSD arrays)
innodb_read_io_threads = 8
# Write I/O threads
innodb_write_io_threads = 8
# Use all CPU cores for purge operations (MySQL 8.0 default: 4)
innodb_purge_threads = 4
# In-memory temporary tables (MySQL 8.0)
tmp_table_size = 256M
max_heap_table_size = 256M
# Sort and join buffer -- per-query allocation, not global
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 2M
The most consistent finding in MySQL performance audits of UK SME databases is that innodb_buffer_pool_size has never been changed from the default 128MB, regardless of how much RAM the server has. This is not a misconfiguration from negligence -- it is a documentation gap. MySQL's default install does not warn the administrator that this setting must be changed for any production use, and most hosting control panels (cPanel, Plesk) do not expose it in their GUI.
A specific example from a London e-commerce client running WooCommerce: the client had a dedicated server with 32GB RAM running MySQL 8.0. SHOW STATUS LIKE 'Innodb_buffer_pool%' showed a buffer pool hit rate of 62% -- meaning 38% of data reads were going to disk. innodb_buffer_pool_size was set to 128MB. The server had 32GB of RAM. We changed innodb_buffer_pool_size to 22G, restarted MySQL, and waited 20 minutes for the buffer pool to warm up. The hit rate climbed to 99.4%. Average query time across the site dropped from 340ms to 87ms -- a 74% reduction. Cart abandonment on the checkout page had been a known business problem for 18 months. The fix took 4 minutes to implement after identifying the root cause.
The same pattern appears in Laravel applications on DigitalOcean, Linode, and Hetzner droplets: the developer provisions a 16GB or 32GB VPS, installs MySQL via apt install mysql-server, and never touches the configuration. Six months later, as the application database grows and query patterns become more complex, performance degrades. The assumption is that the VPS needs upgrading. The actual problem is a configuration setting unchanged from its minimal-resource default.
This is the type of issue a structured database audit surfaces in the first 15 minutes. See our complete database performance tuning guide for the full 15-step audit checklist covering MySQL, PostgreSQL and MSSQL.
The MySQL slow query log records every query that exceeds a configurable time threshold. It is the correct starting point for any MySQL performance investigation -- it shows you which queries are actually slow in production, not which queries look expensive in a code review.
Enabling the slow query log:
# In my.cnf (requires MySQL restart)
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0 # Log queries > 1 second
log_queries_not_using_indexes = ON # Log full table scans regardless of time
min_examined_row_limit = 1000 # Only log if > 1000 rows examined (reduces noise)
# Enable at runtime (no restart required, but resets on restart)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;
SET GLOBAL log_queries_not_using_indexes = ON;
Analysing the slow query log with mysqldumpslow:
# Sort by total execution time (most impactful queries first)
mysqldumpslow -s t -t 20 /var/log/mysql/slow.log
# Sort by count (most frequently executed slow queries)
mysqldumpslow -s c -t 20 /var/log/mysql/slow.log
# Sort by average execution time (consistently slow queries)
mysqldumpslow -s at -t 20 /var/log/mysql/slow.log
# Sample output format:
# Count: 847 Time=3.42s (2897s) Lock=0.00s (0s) Rows=1250.0 (1058750)
# SELECT * FROM orders WHERE YEAR(order_date) = 2026
#
# Count: 847 executions in log window
# Time=3.42s: average execution time
# (2897s): total execution time in log window
# This query accounts for 2,897 seconds of DB time -- top priority to fix
Using pt-query-digest for deeper analysis (Percona Toolkit):
# Install Percona Toolkit
sudo apt-get install percona-toolkit
# Analyse slow query log
pt-query-digest /var/log/mysql/slow.log
# Output includes:
# - Ranked queries by total execution time
# - Execution count, average time, min/max time
# - Query fingerprint (abstracted form showing pattern)
# - Percentile response times (P50, P95, P99)
# Filter for queries on a specific table
pt-query-digest --filter '$event->{arg} =~ /orders/' /var/log/mysql/slow.log
Common slow query patterns and their fixes:
-- PATTERN 1: Function on indexed column prevents index use
-- Slow: YEAR() wrapper means full table scan on order_date index
SELECT * FROM orders WHERE YEAR(order_date) = 2026;
-- Fix: range condition on the column directly
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';
-- PATTERN 2: Leading wildcard LIKE cannot use B-tree index
-- Slow: % at start of LIKE pattern = full table scan
SELECT * FROM products WHERE name LIKE '%widget%';
-- Fix: use FULLTEXT index for substring search
-- First: ALTER TABLE products ADD FULLTEXT idx_ft_name (name);
SELECT * FROM products
WHERE MATCH(name) AGAINST('widget' IN BOOLEAN MODE);
-- PATTERN 3: OR conditions can prevent index use
-- Slow: OR may cause MySQL to scan both indexes separately
SELECT * FROM orders WHERE customer_id = 12345 OR status = 'pending';
-- Fix: UNION ALL approach uses each index efficiently
SELECT * FROM orders WHERE customer_id = 12345
UNION ALL
SELECT * FROM orders WHERE status = 'pending' AND customer_id != 12345;
-- PATTERN 4: NOT IN with NULL values
-- Slow: NOT IN returns empty set if subquery returns any NULL
SELECT * FROM orders WHERE customer_id NOT IN
(SELECT id FROM blocked_customers);
-- Fix: NOT EXISTS handles NULLs correctly and often uses better plan
SELECT * FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM blocked_customers bc WHERE bc.id = o.customer_id
);
Performance Schema for live query monitoring:
-- Find currently running queries taking > 5 seconds
SELECT
id, user, host, db, command, time, state,
SUBSTRING(info, 1, 200) AS query_preview
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 5
ORDER BY time DESC;
-- Top queries by total latency via Performance Schema
SELECT
DIGEST_TEXT AS query,
COUNT_STAR AS executions,
ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_seconds
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
MySQL has a per-connection memory overhead of approximately 1-4MB per thread, including the thread stack, connection buffer and sort/join buffers. At 200 concurrent connections, that is 200-800MB consumed by connection overhead before any query work begins. Connection pooling reduces the number of active backend connections while supporting a much larger number of application threads.
ProxySQL for MySQL connection pooling:
ProxySQL is the most capable MySQL connection pooler. It multiplexes application connections to a smaller pool of MySQL backend connections, supports read/write splitting (routing reads to replicas automatically), and provides query caching and query rewriting capabilities at the proxy layer.
-- ProxySQL admin interface configuration
-- Connect: mysql -u admin -padmin -h 127.0.0.1 -P 6032
-- Add MySQL backend servers
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES
(0, '127.0.0.1', 3306, 1000), -- hostgroup 0 = writes (primary)
(1, '10.0.0.2', 3306, 1000); -- hostgroup 1 = reads (replica)
-- Connection pool settings
UPDATE global_variables
SET variable_value = '200'
WHERE variable_name = 'mysql-max_connections'; -- Max application connections
UPDATE global_variables
SET variable_value = '10'
WHERE variable_name = 'mysql-default_max_latency_ms';
-- Apply configuration
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
-- Query routing rule: route SELECT queries to replica (hostgroup 1)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup)
VALUES (1, 1, '^SELECT', 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;
Laravel connection pool configuration:
Laravel uses PHP PDO for MySQL connections. Within a standard PHP-FPM setup, each PHP worker process maintains its own database connection (PHP is stateless per request). The effective connection count is the number of PHP-FPM worker processes. For high-traffic applications, PHP-FPM's pm.max_children controls this:
; /etc/php/8.3/fpm/pool.d/www.conf
pm = dynamic
pm.max_children = 50 ; Max PHP workers = max DB connections from this server
pm.start_servers = 10
pm.min_spare_servers = 5
pm.max_spare_servers = 20
pm.max_requests = 1000 ; Recycle workers to prevent memory leaks
; Set max_children based on: available_memory / memory_per_worker
; Typical Laravel app: 30-60MB per worker
; 8GB available for PHP: 8192 / 50 = ~160MB per worker (comfortable)
MySQL server-side connection settings:
[mysqld]
# Maximum simultaneous connections
max_connections = 300
# Thread cache -- reuse threads for reconnecting clients
# Reduces connection establishment overhead
thread_cache_size = 64
# Idle connection timeout
wait_timeout = 300 # Close idle connections after 5 minutes
interactive_timeout = 300
# Back log -- queue for incoming connections when max_connections is reached
back_log = 80
# Check current connection usage
-- SHOW STATUS LIKE 'Threads_connected';
-- SHOW STATUS LIKE 'Max_used_connections';
-- If Max_used_connections approaches max_connections, increase pool size
For UK businesses running Laravel applications on a single server, the correct approach is: set max_connections to 1.5-2x your PHP-FPM pm.max_children, configure thread_cache_size equal to pm.max_children, and set wait_timeout to 300 seconds to reclaim idle connections from queue workers and cron jobs that do not explicitly close their connections.
For more detail on the full range of MySQL, PostgreSQL and MSSQL performance techniques, see our database performance tuning guide.
The single most impactful MySQL configuration setting is innodb_buffer_pool_size. It should be set to 60-80% of total server RAM on a dedicated MySQL server. The default of 128MB is designed for minimal hardware and causes excessive disk I/O on any server with more than 1GB of RAM. After changing this setting and restarting MySQL, verify the buffer pool hit rate reaches above 99% using SHOW STATUS LIKE 'Innodb_buffer_pool%'. This change alone typically reduces average query times by 50-80% on servers that have been running with the default.
Run EXPLAIN before any slow query to inspect the execution plan. The type column shows how MySQL accesses the table. ALL means a full table scan (no index used). ref or range means an index is being used efficiently. The key column shows which index MySQL chose; if it shows NULL, no index was used. The rows column shows how many rows MySQL estimates it must examine -- a high number on a large table with a narrow WHERE clause indicates a missing index.
In Laravel, N+1 query problems are fixed by adding with() calls to Eloquent queries to eager load relationships. For example, change Order::all() (which generates one query per order to fetch the customer) to Order::with('customer')->get() (which fetches all orders and all related customers in two queries total). You can detect N+1 problems in development by adding the Laravel Debugbar package, which shows the total query count per page load. A count above 50 queries on a simple page almost always indicates an N+1 pattern.
ProxySQL is a high-performance MySQL proxy that provides connection pooling, read/write splitting and query routing. UK businesses need ProxySQL when they have read replicas and want automatic routing of SELECT queries to the replica without changing application code, or when the number of application threads (PHP-FPM workers, queue workers, cron processes) across multiple servers exceeds MySQL's max_connections setting. Single-server deployments with fewer than 150 concurrent application threads typically do not need ProxySQL -- MySQL's thread cache handles reconnection overhead adequately.
A structured MySQL performance audit for a UK SME application typically takes 2-4 hours of investigation time. The first hour involves enabling slow query logging, collecting 24-48 hours of slow query data, and running pt-query-digest to rank queries by total execution time. The second hour involves running EXPLAIN on the top 20 slow queries and identifying missing indexes and rewritable patterns. Implementation of the identified fixes -- typically adding 3-8 composite indexes and adjusting server configuration -- takes an additional 1-2 hours including testing. The most impactful improvements are usually visible within minutes of the first index additions.
MySQL performance tuning for UK businesses comes down to five specific actions: set innodb_buffer_pool_size to 60-80% of server RAM; enable the slow query log and identify your top 10 slowest queries by total execution time; add composite covering indexes on the filtered columns of those queries; fix N+1 patterns in application ORM code; and configure connection pooling appropriate to your concurrency level. The default MySQL installation is not configured for production performance -- it is configured for minimal resource usage. Every UK business running MySQL on a server with more than 2GB of RAM has performance gains available from configuration changes alone, before touching a single index or query.
Softomate Solutions provides MySQL performance tuning for UK businesses from our base in Stanmore, London. Request a free initial database assessment.
Written by the Softomate Solutions technical team, database and software specialists based in Stanmore, London.
More from the blog
Let us help
Talk to our London-based team about how we can build the AI software, automation, or bespoke development tailored to your needs.
Softomate Solutions Limited
Deen Dayal Yadav
Online
