MySQL Performance Optimization: Diagnosing & Fixing Slow Queries

A slow database makes every page slow, and on shared hosting it also holds PHP requests open until your account runs into its limits. Most slow queries have the same few causes: a missing index, a query that reads far more rows than it returns, or a table that has grown for years without clean-up. This guide shows how to find the slow query, read EXPLAIN, fix it with the right index, and which server settings matter only when you run your own server.

1. Where the time actually goes

A query is slow when it examines many rows to return a few. A table with 500,000 orders and no index on customer_id means MySQL reads all 500,000 rows every time someone opens "My orders". The fix is almost never more hardware; it is letting MySQL jump straight to the rows it needs.

Other common causes:

• Sorting without an index (ORDER BY on an unindexed column), which shows up as Using filesort.
• SELECT * on wide tables, pulling text and blob columns nobody displays.
• N+1 queries: one query for a list, then one more query per row, often hidden inside a plugin or ORM loop.
• Bloated tables, such as a WordPress wp_options table full of autoloaded data, or years of revisions and expired transients.
• Lock waits, where one long write blocks everyone reading the same rows.

2. Find the slow queries

SHOW PROCESSLIST; (or SHOW FULL PROCESSLIST;) shows what is running right now. On shared hosting you see only your own database user's connections, which is enough to spot a query stuck for many seconds.

3. Read EXPLAIN

Put EXPLAIN in front of any SELECT to see how MySQL plans to run it:

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

The columns that matter:

On MySQL 8.0.18 and later you can run the query for real and see actual times per step (MariaDB uses ANALYZE SELECT ... for the same job):

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE customer_id = 42;

EXPLAIN ANALYZE executes the query, so don't use it on a slow UPDATE or DELETE on a live site.

4. Fix it with the right index

Check what already exists:

SHOW INDEX FROM orders;

Then add an index that matches how the query filters and sorts. For the query above, one composite index covers both the WHERE and the ORDER BY:

ALTER TABLE orders ADD INDEX idx_customer_created (customer_id, created_at);

Rules that hold in practice:

• Column order matters. Put the columns you compare with = first, then the range or sort column. An index on (customer_id, created_at) helps WHERE customer_id = ? ORDER BY created_at, but not a query that filters only on created_at.
• One good composite index beats several single-column ones for multi-column filters.
• Prefix indexes for long text. ADD INDEX idx_email (email(50)) keeps the index small. Prefix indexes can't satisfy an ORDER BY on that column.
• LIKE '%word%' can't use a normal index. For word search, use a FULLTEXT index with MATCH ... AGAINST.
• Don't index everything. Each index slows inserts and updates and uses disk. Remove indexes that EXPLAIN never picks.

5. Write lighter queries

• Select only the columns you use: SELECT id, name instead of SELECT *.
• Page large results with LIMIT. For deep pages, use keyset pagination (WHERE id < last_seen_id ORDER BY id DESC LIMIT 20) instead of a large OFFSET, which still reads every skipped row.
• Replace N+1 loops with one query using JOIN or WHERE id IN (...).
• Don't wrap indexed columns in functions. WHERE DATE(created_at) = '2026-09-01' can't use an index; WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02' can.
• Use prepared statements (PDO or mysqli). They protect against SQL injection, and the plan stays predictable.
• Clean up. On WordPress, remove old revisions, spam comments and expired transients, and check wp_options for large autoloaded rows. Take a backup first.

6. Server settings: only on your own VPS or server

Everything in this section applies to a MySQL or MariaDB server you manage, such as your own VPS. On shared hosting the database server is shared by many accounts, so you can't edit my.cnf, restart MySQL or read the server's slow query log. Tune your queries and indexes instead, and ask support if you think something is wrong on the server side.

Slow query log. Add under [mysqld] (/etc/my.cnf on AlmaLinux, /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu with MySQL), make sure the log folder exists and is owned by the mysql user, and restart the service:

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

Summarise it with mysqldumpslow -s t /var/log/mysql/slow.log | head -30, or pt-query-digest from Percona Toolkit.

Settings worth checking:

Change one setting at a time and measure. Copying an "optimised my.cnf" from the internet, written for someone else's RAM and workload, often makes things worse.

7. Running this on Domain India

Shared hosting (cPanel, DirectAdmin, Webuzo). Use phpMyAdmin in your control panel to run EXPLAIN and add indexes. Port 3306 is closed to outside connections on our shared servers, so desktop tools such as MySQL Workbench or DBeaver need an SSH tunnel. Jailed SSH access is available on every shared hosting plan (cPanel, DirectAdmin, Webuzo). It is off by default; ask support to enable it for your account. Login is with an SSH key. See securing MySQL access with SSH tunnels. If slow queries are pushing your account into its limits, check your resource usage, and for database errors see database troubleshooting.

VPS. Our VPS is self-managed with full root access, so every setting in section 6 is yours to tune.

App Platform. Every plan includes a managed PostgreSQL database, if you are building a new app rather than tuning an existing MySQL one.

Ready to speed up your database? Work through my website is slow, connect safely with how to connect to the MySQL database, or compare VPS plans if you need full control of MySQL.