Slow MySQL Queries: How to Diagnose and Optimize Them

To optimize slow MySQL queries on a website, start by identifying the slowest queries using MySQL's built-in slow query log or the EXPLAIN statement. Once found, the fix almost always involves adding the right indexes, rewriting inefficient joins, or applying application-level caching.

This guide takes you step by step from diagnosis to fix, with real examples and free tools.

How to Confirm MySQL Is the Bottleneck

Before touching any query, verify the database is actually the problem. Clear signals:

• TTFB exceeds 800 ms even though the server shows available CPU and RAM.
• Slowness only affects pages with listings, search results, or reports — not static pages.
• Loading the same page repeatedly produces inconsistent load times (a table scan with no index runs in full on every request).

Initial diagnostic tools:

• Query Monitor (free WordPress plugin): displays every executed query, its duration, and the call stack.
• New Relic or Datadog (paid): transaction-level traces with detailed database breakdowns.
• MySQL slow query log: the engine's native option — no extra software needed.

Step 1 — Enable and Read the Slow Query Log

The slow query log automatically records every query that exceeds a time threshold. To enable it for a live session (no MySQL restart required):

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;   -- seconds; lower to 0.5 on busy sites
SET GLOBAL slow_query_log_file = '/tmp/mysql-slow.log';

Let normal traffic run for 15–30 minutes, then analyze the log with mysqldumpslow:

mysqldumpslow -s t -t 10 /tmp/mysql-slow.log

The -s t flag sorts by total time; -t 10 shows the 10 worst offenders. Copy those queries for the next step.

Step 2 — Analyze with EXPLAIN

Prepend EXPLAIN to any suspect SELECT:

EXPLAIN SELECT p.id, p.title, u.name
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY p.created_at DESC
LIMIT 20;

The most important output columns:

If you see type: ALL on a table with more than 10,000 rows, adding an index is the single highest-impact fix you can make.

Step 3 — Add the Right Indexes

For the example query above, the optimal indexes are:

-- Composite index for WHERE + ORDER BY
ALTER TABLE posts ADD INDEX idx_status_created (status, created_at);

-- Index on the foreign key used in the JOIN (if missing)
ALTER TABLE posts ADD INDEX idx_user_id (user_id);

Golden rules for indexing:

• Always index columns that appear in WHERE, JOIN ON, and ORDER BY.
• Composite indexes outperform multiple single-column indexes when columns are always filtered together.
• Don't index low-cardinality columns (e.g. a boolean with only two possible values).
• Every extra index slows writes (INSERT/UPDATE); only add what EXPLAIN specifically flags.

Step 4 — Rewrite Inefficient Queries

Sometimes the index exists but the query is written in a way that MySQL can't use it. Common patterns:

Function Applied to an Indexed Column

-- BAD: YEAR() prevents MySQL from using the index on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- GOOD: date range that uses the index
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Unnecessary SELECT *

-- BAD: fetches all columns, including large TEXT fields
SELECT * FROM posts WHERE status = 'published';

-- GOOD: only the columns the page actually needs
SELECT id, title, excerpt, created_at FROM posts WHERE status = 'published';

Correlated Subquery Instead of JOIN

-- BAD: correlated subquery runs once per row
SELECT id, (SELECT name FROM users WHERE id = posts.user_id) AS author
FROM posts;

-- GOOD: JOIN is far more efficient with an index on users.id
SELECT posts.id, users.name AS author
FROM posts
JOIN users ON users.id = posts.user_id;

For more database and server optimization techniques alongside caching and compression strategies, browse the performance blog.

Step 5 — Caching and Server Configuration

Once queries are well-written and properly indexed, consider these caching layers:

• MySQL Query Cache (MySQL 5.7 or earlier): useful for highly repetitive reads, but deprecated in MySQL 8 and MariaDB 10.5+.
• Application-level caching: store frequent query results in Redis or Memcached with a short TTL (30–300 seconds). This is the most scalable approach.
• Tune innodb_buffer_pool_size: on a dedicated server, set this to 70–80% of total RAM. On shared hosting you can't control it, but it's worth asking your provider to review it.

If performance problems persist after query optimization and caching, it may be time to upgrade to a plan with dedicated database resources. The team at elenlace.com can help you evaluate the right hosting architecture for your traffic volume.

Key Takeaways

• The slow query log is the most effective free tool for identifying your worst-performing queries.
• EXPLAIN reveals missing indexes (type: ALL) and disk-based sorts (Using filesort) at a glance.
• Adding indexes on WHERE, JOIN, and ORDER BY columns delivers the highest impact in most sites.
• Avoid functions on indexed columns, unnecessary SELECT *, and correlated subqueries.
• Application-level caching with Redis or Memcached is the logical next step once queries are optimized.

Still seeing slow load times after applying these techniques? The experts at elenlace.com offer complete performance audits — from query analysis to server configuration — and deliver a concrete action plan.

FAQ

How long does a MySQL query need to take before it's considered "slow"?

The standard threshold is 1 second, but user experience starts degrading at 200–300 ms. On high-traffic sites, any production query exceeding 100 ms is worth reviewing.

Can I optimize MySQL queries on shared hosting without SSH access?

Yes. You can run EXPLAIN and ALTER TABLE ... ADD INDEX directly from phpMyAdmin or Adminer without SSH. The slow query log requires superuser permissions, but plugins like Query Monitor (for WordPress) provide equivalent information from the admin dashboard.

Can adding indexes break my database?

No. An index is a read-side structure; it never modifies your data. What can happen on very large tables is that ALTER TABLE with millions of rows takes several minutes and blocks writes. In that case, use pt-online-schema-change or gh-ost to add the index without downtime.

Does WordPress have specific tools for diagnosing slow queries?

Yes. The free Query Monitor plugin displays every query executed on each page load in the WordPress admin bar, grouped by source and sorted by duration. It's the ideal starting point for any WordPress site before touching the database directly.

Further reading

Other providers and guides worth comparing:

• IONOS
• GoDaddy
• HostGator México