How to fix slow MySQL queries
To fix slow MySQL queries, find out which queries are slow before you change anything. Switch on the slow query log, let it collect real traffic, summarise it with mysqldumpslow, then run EXPLAIN on the worst statements. Most fixes turn out to be a missing index, a WHERE clause that stops an index being used, or an application sending hundreds of small queries. Server settings such as the InnoDB buffer pool come after that, once the queries themselves are sound.
The commands follow the MySQL 8.4 manual and also work on 8.0; MariaDB differences are noted.
Quick checklist
- Enable the slow query log with a threshold of one second or less.
- Summarise the log with
mysqldumpsloworpt-query-digestand pick the queries with the highest total time. - Run
EXPLAINandEXPLAIN ANALYZEon each one. - Add or reorder indexes, and rewrite conditions that cannot use an index.
- Run
ANALYZE TABLEafter large data changes. - Check the InnoDB buffer pool size and disk I/O.
Step 1: turn on the MySQL slow query log
The slow query log is off by default, and long_query_time defaults to 10 seconds, which is far too high for a website. You can switch it on without a restart:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SHOW VARIABLES LIKE 'slow_query_log_file';
The threshold accepts fractions such as 0.5. slow_query_log takes effect at once, but a new global long_query_time applies only to connections opened after the change. log_queries_not_using_indexes also records fast queries that scan without an index, which can be noisy.
To keep the settings after a restart, add them to the server configuration. On Ubuntu the MySQL package reads /etc/mysql/mysql.conf.d/mysqld.cnf; the MariaDB package uses /etc/mysql/mariadb.conf.d/50-server.cnf:
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 1
Step 2: summarise the log with mysqldumpslow or pt-query-digest
mysqldumpslow ships with MySQL and groups similar statements by replacing numbers with N and strings with 'S':
sudo mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
-s t sorts by total query time and -t 10 shows the top ten. Total time matters more than the average: a query run 50,000 times a day costs more than a rare slow report. For a fuller report, pt-query-digest from Percona Toolkit ranks query fingerprints by total response time:
pt-query-digest /var/log/mysql/mysql-slow.log > digest.txt
Step 3: read the plan with MySQL EXPLAIN
Put EXPLAIN in front of a slow SELECT to see how the optimiser plans to run it, without running it:
EXPLAIN SELECT id, total, created_at FROM orders
WHERE customer_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;
| Column | What to look for |
|---|---|
type | ALL means a full table scan; ref, range or const mean an index is used. |
key | The index chosen. NULL means none. |
rows | Estimated rows examined. Compare it with the rows you actually need. |
Extra | Using filesort or Using temporary point to extra sorting work; Using index means the index alone answered the query. |
EXPLAIN ANALYZE in MySQL 8.0.18 and later
EXPLAIN ANALYZE actually runs the statement and prints a tree with estimated cost and rows next to the real time, rows and loops for each step. A large gap between estimated and actual rows usually means stale statistics or a poor index. Because it executes the statement, keep it away from UPDATE and DELETE on live data. On MariaDB, the equivalent is the ANALYZE statement, for example ANALYZE FORMAT=JSON SELECT ..., which adds real row counts and timings to the plan.
Step 4: add the right indexes
For the query above, one composite index serves both the filter and the sort:
CREATE INDEX idx_cust_status_created
ON orders (customer_id, status, created_at);
Column order matters. MySQL can use any leftmost prefix of a composite index, so this index also helps WHERE customer_id = 42 on its own, but not WHERE status = 'paid' on its own. Put columns tested with = first and the range or ORDER BY column last.
A covering index holds every column the query reads, so MySQL never touches the table rows and EXPLAIN shows Using index. InnoDB secondary indexes already carry the primary key, so if id is the primary key, only total is missing here; adding it to the end of the index would make the query covered. Do not index every column: each index slows inserts and updates.
Step 5: optimize MySQL queries that cannot use an index
- Avoid
SELECT *. Name the columns you need. It moves less data and makes covering indexes possible. - No functions on indexed columns.
WHERE DATE(created_at) = '2026-11-01'forces a scan. Use a range instead:WHERE created_at >= '2026-11-01' AND created_at < '2026-11-02'. The same applies toLOWER(),YEAR()and comparing a string column with a number. - No leading wildcard.
LIKE 'shoe%'can use an index;LIKE '%shoe'cannot. For word searches, use aFULLTEXTindex withMATCH ... AGAINST. - Remove N+1 queries. Code that loads 50 posts and then runs one query per post for its author sends 51 queries. Each is fast, so none reaches the slow log, but together they are slow. Fetch the related rows with a
JOINor oneWHERE id IN (...)query.
Step 6: refresh statistics with ANALYZE TABLE
The optimiser chooses a plan from index statistics. InnoDB recalculates them after about 10% of a table changes, but after a bulk import or large delete, refresh them by hand:
ANALYZE TABLE orders;
Step 7: size the InnoDB buffer pool and check disk I/O
InnoDB keeps table and index pages in the buffer pool, which defaults to only 128 MB. When the working data does not fit, every query waits on disk. Check the size and how often reads miss memory:
SELECT @@innodb_buffer_pool_size / 1024 / 1024 AS pool_mb;
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
Innodb_buffer_pool_reads counts reads that had to go to disk; Innodb_buffer_pool_read_requests counts all reads. If disk reads are more than a small fraction of requests, the pool is too small. On a server that runs only the database, 50–70% of RAM is a safe starting point; the manual allows up to 80%, and InnoDB uses about 10% more than the figure you set. If the same VPS also runs the web server and PHP, set it lower so the server never swaps. How much RAM a VPS needs covers the sizing. The pool can be resized online:
SET GLOBAL innodb_buffer_pool_size = 4294967296;
That example is 4 GB. MySQL rounds it to a multiple of innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances; add the same value to the config file so it survives a restart. To check disk I/O, run iostat -x 1 (package sysstat) during the slow period and watch %util, r_await and w_await.
Why is import into MySQL taking so long?
With autocommit on, each INSERT is its own transaction and forces a log flush to disk; single-row inserts add a round trip per row; and unique and foreign key checks run on every row. When you create the dump, keep mysqldump's default --opt group, which writes multi-row INSERT statements, and add --single-transaction for a consistent InnoDB dump without locking tables:
mysqldump -u root -p --single-transaction --routines shop > shop.sql
Import from the command line rather than phpMyAdmin, which can time out on large files, and turn off autocommit so rows are committed in large batches instead of one at a time:
mysql -u root -p shop
SET autocommit = 0;
SET unique_checks = 0;
SET foreign_key_checks = 0;
SOURCE /root/shop.sql;
COMMIT;
SET unique_checks = 1;
SET foreign_key_checks = 1;
Files made by mysqldump already switch off unique and foreign key checks in their header, so those two lines matter most for hand-made or third-party SQL files. Switch the checks off only for data you trust, because duplicate keys and broken references are not caught while they are off.
Slow MySQL on WordPress: autoloaded options and Query Monitor
On WordPress, install the free Query Monitor plugin. It lists every query on a page with its time, the plugin or theme that ran it, and duplicates, which exposes N+1 patterns quickly. Then check the autoloaded options, which WordPress loads on every request:
SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
ORDER BY bytes DESC LIMIT 20;
Change wp_ if your table prefix differs. Large entries often belong to removed plugins; back up before deleting any. If slow queries pile up until the server refuses new connections, WordPress shows Error establishing a database connection; that guide covers the connection side. Caching, covered in WordPress speed optimisation, cuts how many queries run at all.
Fixing slow MySQL queries on Bigdomainhost
On Web & cPanel and WordPress hosting, databases are MySQL or MariaDB managed in phpMyAdmin, where you can run EXPLAIN, ANALYZE TABLE and CREATE INDEX from the SQL tab. Server-wide settings such as the slow log cannot be changed on shared hosting, so use Query Monitor and EXPLAIN instead.
On a KVM VPS you have full root access and reserved NVMe storage, so every step in this guide applies. Take a snapshot before a large index change or a config edit; the out-of-band console in the control panel gets you in if a change stops the server responding. For help reading a plan, call +91 75994 50220 during IST hours, WhatsApp the same number at any time, or email support@bigdomainhost.com.
Sources (checked )
- MySQL 8.4 Reference Manual: The slow query log
- MySQL 8.4 Reference Manual: mysqldumpslow: summarise slow query log files
- MySQL 8.4 Reference Manual: EXPLAIN statement
- MySQL 8.4 Reference Manual: Multiple-column indexes
- MySQL 8.4 Reference Manual: Bulk data loading for InnoDB tables
- MySQL 8.4 Reference Manual: Configuring InnoDB buffer pool size