Talk to the NOC, IST hours — +91 75994 50220 support@bigdomainhost.com
Guide

Find and fix slow MySQL queries, step by step

Slow pages are often slow queries. This guide shows how to find the queries that cost the most time, read their plans and fix them with indexes, rewrites and the right server settings.

Short answer: To fix slow MySQL queries, enable the slow query log with long_query_time set to one second, summarise it with mysqldumpslow, and run EXPLAIN or EXPLAIN ANALYZE on the worst queries. Add composite or covering indexes, avoid functions on indexed columns and leading-wildcard LIKE, run ANALYZE TABLE, then check the InnoDB buffer pool size.

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

  1. Enable the slow query log with a threshold of one second or less.
  2. Summarise the log with mysqldumpslow or pt-query-digest and pick the queries with the highest total time.
  3. Run EXPLAIN and EXPLAIN ANALYZE on each one.
  4. Add or reorder indexes, and rewrite conditions that cannot use an index.
  5. Run ANALYZE TABLE after large data changes.
  6. 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;
ColumnWhat to look for
typeALL means a full table scan; ref, range or const mean an index is used.
keyThe index chosen. NULL means none.
rowsEstimated rows examined. Compare it with the rows you actually need.
ExtraUsing 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 to LOWER(), 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 a FULLTEXT index with MATCH ... 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 JOIN or one WHERE 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 )

Answers

Frequently asked questions

Still unsure? Call +91 75994 50220 or write to support@bigdomainhost.com — a human replies in IST hours.

Why is import into MySQL taking so long?

Usually because every row is committed separately. With autocommit on, each INSERT forces a log flush, and single-row inserts add a round trip per row. Dump with mysqldump's default multi-row inserts, import from the command line, and wrap the load in SET autocommit = 0 and COMMIT. Switching off unique and foreign key checks during a trusted import also helps.

How do I enable the MySQL slow query log?

Run SET GLOBAL slow_query_log = 'ON' and SET GLOBAL long_query_time = 1 as an admin user, then SHOW VARIABLES LIKE 'slow_query_log_file' to find the file. The new threshold applies to new connections. Add slow_query_log and long_query_time under [mysqld] in the server config so the settings survive a restart.

What does MySQL EXPLAIN show?

EXPLAIN shows the plan the optimiser chose without running the query: the access type for each table, the index used, the estimated rows examined and extra work such as filesorts or temporary tables. A type of ALL with key NULL means a full table scan. EXPLAIN ANALYZE, from MySQL 8.0.18, runs the query and adds real timings.

How do I optimize MySQL queries without changing server settings?

Select only the columns you need, add composite indexes that match your WHERE and ORDER BY clauses, and keep indexed columns free of functions. Replace leading-wildcard LIKE searches with a FULLTEXT index, and replace loops of small queries with one JOIN or IN query. Then confirm each change with EXPLAIN and the slow log.

How much RAM should the InnoDB buffer pool use?

On a server that runs only MySQL, 50 to 70 percent of RAM is a safe start, and the manual allows up to 80 percent. InnoDB uses about 10 percent more than the value you set. If the web server and PHP share the machine, set it lower so the server never starts swapping.

Support that picks up the phone.

In IST hours, in Hindi or English, and WhatsApp replies fastest at any time. Sales, migration and emergencies all reach the same engineers. No offshore queue, no 48-hour first reply.

Need cPanel or WHM with the ports already sorted?

Web & cPanel hosting comes with cPanel ready to log in to, reseller hosting adds white-label WHM, and a KVM VPS can have cPanel & WHM pre-installed. Call +91 75994 50220 or email support@bigdomainhost.com.