How do I diagnose and fix slow database queries in MySQL on hosting?

Slow MySQL queries are the most common cause of high server load and slow website responses. The MySQL slow query log identifies queries taking over a configurable threshold. EXPLAIN shows query execution plans, and strategic index creation typically reduces query time by 10-1000x.

DETAILED EXPLANATION:
Most common slow query causes:
1. Missing indexes: Full table scan instead of index lookup
2. N+1 query problem: Loop executing one query per iteration
3. SELECT star: Fetching all columns when only 2-3 needed
4. No LIMIT clause: Returning millions of rows
5. String LIKE with leading wildcard: LIKE '%keyword%' prevents index use
6. Implicit type conversion: Comparing integer column with string value
7. Large temp tables: Complex JOINs creating huge temporary result sets

Index types and when to use:
B-Tree Index (default): Range queries, equality, ORDER BY
Hash Index: Equality only (no ranges), faster for exact match
FULLTEXT Index: Text search, MATCH() AGAINST() queries
Composite Index: Multiple columns, queries filtering on multiple conditions

STEP-BY-STEP - MySQL performance diagnosis and optimization:

1. Enable slow query log:
Connect Quest cPanel > MySQL > phpMyAdmin > SQL tab:
SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1; (log queries over 1 second)
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Or in /etc/mysql/mysql.conf.d/mysqld.cnf:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

2. Analyze slow query log with pt-query-digest:
apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log

Output example:
Rank Response time Calls R/Call Query
1 45.2355 81.3% 12847 0.0035 SELECT wp_posts.* FROM wp_posts WHERE...
2 5.1234 9.2% 1243 0.0041 SELECT * FROM wp_options WHERE option_name LIKE...

3. Examine worst query with EXPLAIN:
EXPLAIN SELECT wp_posts.*, wp_postmeta.*
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id
WHERE wp_posts.post_status = 'publish'
AND wp_postmeta.meta_key = 'price'
ORDER BY wp_posts.post_date DESC
LIMIT 20;

EXPLAIN output:
id | type | table | key | rows | Extra
1 | ALL | wp_posts | NULL | 45000 | Using where; Using filesort
2 | ALL | wp_postmeta | NULL | 89000 | Using where

type = ALL means full table scan. rows = 45000 scanned for LIMIT 20.
This is the problem.

4. Create indexes to fix:
Add composite index for this query pattern:
ALTER TABLE wp_posts ADD INDEX idx_status_date (post_status, post_date);
ALTER TABLE wp_postmeta ADD INDEX idx_post_meta_key (post_id, meta_key);

Run EXPLAIN again:
type | key | rows | Extra
ref | idx_status_date | 850 | Using where; Using index
ref | idx_post_meta_key| 3 | Using where

Rows scanned: 45,000 -> 850 (53x reduction)
Query time: 450ms -> 8ms

5. WordPress-specific optimizations:

Fix autoloaded options bloat (common WordPress issue):
SELECT LENGTH(option_value) as size, option_name
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

If many rows above 1 MB total autoloaded:
UPDATE wp_options SET autoload = 'no'
WHERE option_name IN ('woocommerce_session_XXXX', 'transient_XXXX');

Clean transients:
DELETE FROM wp_options WHERE option_name LIKE '_transient_%';

Fix WooCommerce order meta bloat:
OPTIMIZE TABLE wp_postmeta;
OPTIMIZE TABLE wp_options;

6. InnoDB buffer pool tuning (/etc/mysql/mysql.conf.d/innodb.cnf):
For 8 GB RAM server, allocate 60% to InnoDB:
innodb_buffer_pool_size = 4G
innodb_buffer_pool_instances = 4 (one per GB for pools > 1 GB)
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2 (slightly less durable, much faster)

REAL EXAMPLES:
Before index optimization (WooCommerce product search):
Query: SELECT * FROM wp_posts WHERE post_type='product' AND post_status='publish'
Query time: 847ms | Rows examined: 45,000

After index (idx_post_type_status added):
Same query: 8ms | Rows examined: 847 (53x faster)

Autoloaded options cleanup impact:
Before: wp_options autoloaded = 8.4 MB (WooCommerce session data)
After cleanup + Redis sessions: 120 KB autoloaded
Effect: Every WordPress page load saves 200ms DB read time

FLOW:
Slow page load -> Enable slow query log -> pt-query-digest identifies worst queries
-> EXPLAIN shows full table scans -> CREATE INDEX to allow index lookup
-> Verify with EXPLAIN (type should be ref or range, not ALL)
-> OPTIMIZE TABLE reduces fragmentation -> Monitor slow log for new slow queries

KEY POINTS:
- Index creation is the single highest-ROI database optimization (free, immediate impact)
- pt-query-digest is far more valuable than looking at raw slow query log
- InnoDB buffer pool should hold your entire working dataset in RAM
- Connect Quest VPS with dedicated MySQL RAM allocation performs better than shared hosting MySQL

COMMON MISTAKES:
- Adding indexes on every column (index maintenance overhead slows INSERT/UPDATE)
- Ignoring long_query_time = 0 (log EVERYTHING temporarily for initial baseline)
- Not monitoring after optimization (new queries added by plugin updates)
- Optimizing queries without EXPLAIN first (guessing index placement)

QUICK FIX:
MySQL using 100% CPU: Run SHOW PROCESSLIST in phpMyAdmin.
Identify long-running queries. KILL [process_id] to terminate runaway query.
Then investigate and add index.

DIFFICULTY: Advanced
RELATED: VPS Hosting, WordPress Performance, Dedicated Servers, Database Hosting