Fix MySQL Query Slow

What's Actually Happening

MySQL queries take too long to execute. Database performance is degraded, affecting application response times.

The Error You'll See

```bash $ mysql -e "SELECT * FROM large_table WHERE name LIKE '%test%'"

# Query takes 30+ seconds ```

Slow query log:

# Query_time: 25.123456  Lock_time: 0.000123
SELECT * FROM orders WHERE customer_id = 123;

Application timeout:

SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

Why This Happens

1. 1.Missing index - Query scanning full table
2. 2.Inefficient query - Poor query structure
3. 3.Large result set - Fetching too much data
4. 4.Lock contention - Queries waiting for locks
5. 5.Resource limits - Insufficient memory or CPU
6. 6.Poor schema design - Bad table structure

Step 1: Identify Slow Queries

```sql -- Enable slow query log: SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2; SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

-- Check slow queries: SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- Or check log file: -- tail -f /var/log/mysql/mysql-slow.log

-- Find queries by time: SELECT query_time, lock_time, sql_text FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10;

-- Check processlist: SHOW FULL PROCESSLIST;

-- Find running long queries: SELECT ID, USER, HOST, DB, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 5; ```

Step 2: Analyze Query Execution

```sql -- Use EXPLAIN: EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- Extended EXPLAIN: EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- Check key columns: -- type: ALL (bad - full scan), index, range, ref, eq_ref, const -- key: index used (NULL = no index) -- rows: rows examined -- Extra: Using filesort, Using temporary (bad)

-- Format EXPLAIN: EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 123;

-- Check table status: SHOW TABLE STATUS LIKE 'orders';

-- Check index usage: SHOW INDEX FROM orders;

-- Analyze table: ANALYZE TABLE orders; ```

Step 3: Add Missing Indexes

```sql -- Find missing indexes: SELECT TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'orders';

-- Add index on frequently queried column: CREATE INDEX idx_customer_id ON orders(customer_id);

-- Composite index for multiple columns: CREATE INDEX idx_customer_status ON orders(customer_id, status);

-- Covering index (include all columns): CREATE INDEX idx_covering ON orders(customer_id, status, total);

-- Full-text index for text search: CREATE FULLTEXT INDEX idx_name ON products(name, description);

-- Check index cardinality: SHOW INDEX FROM orders;

-- Drop unused index: DROP INDEX idx_unused ON orders;

-- Force index usage: SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id = 123; ```

Step 4: Optimize Query Structure

```sql -- BAD: SELECT * SELECT * FROM orders WHERE customer_id = 123;

-- GOOD: Select specific columns SELECT id, order_date, total FROM orders WHERE customer_id = 123;

-- BAD: LIKE with leading wildcard SELECT * FROM products WHERE name LIKE '%phone%';

-- GOOD: Prefix search (uses index) SELECT * FROM products WHERE name LIKE 'phone%';

-- BAD: OR conditions SELECT * FROM orders WHERE customer_id = 123 OR customer_id = 456;

-- GOOD: IN clause SELECT * FROM orders WHERE customer_id IN (123, 456);

-- BAD: Function on indexed column SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

-- GOOD: Range query SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- BAD: Subquery in WHERE SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active');

-- GOOD: JOIN SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id WHERE c.status = 'active';

-- Use LIMIT: SELECT * FROM large_table LIMIT 100; ```

Step 5: Optimize JOINs

```sql -- Check JOIN performance: EXPLAIN SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'pending';

-- Ensure JOIN columns indexed: CREATE INDEX idx_orders_customer ON orders(customer_id); CREATE INDEX idx_customers_id ON customers(id);

-- Use appropriate JOIN type: -- INNER JOIN: Match in both tables -- LEFT JOIN: All from left + matching from right -- Avoid unnecessary JOINs

-- Filter early: -- BAD: SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.created_at > '2024-01-01';

-- GOOD: Filter in JOIN SELECT o.*, c.name FROM orders o JOIN customers c ON o.customer_id = c.id AND o.created_at > '2024-01-01';

-- Use STRAIGHT_JOIN if needed to force order: SELECT /*+ STRAIGHT_JOIN */ o.*, c.name FROM orders o STRAIGHT_JOIN customers c ON o.customer_id = c.id; ```

Step 6: Check Lock Contention

```sql -- Check for locks: SHOW ENGINE INNODB STATUS\G

-- Look for: -- TRANSACTIONS -- LOCK WAIT

-- Check open transactions: SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

-- Find blocking transactions: SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS w JOIN INFORMATION_SCHEMA.INNODB_TRX b ON b.trx_id = w.blocking_trx_id JOIN INFORMATION_SCHEMA.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

-- Kill blocking query: KILL <thread_id>;

-- Check metadata locks: SELECT * FROM PERFORMANCE_SCHEMA.METADATA_LOCKS; ```

Step 7: Optimize Server Settings

```sql -- Check current settings: SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; SHOW VARIABLES LIKE 'query_cache%';

-- Increase buffer pool (should be 70-80% of RAM): SET GLOBAL innodb_buffer_pool_size = 4294967296; -- 4GB

-- Other important settings: -- innodb_log_file_size -- innodb_flush_log_at_trx_commit -- max_connections -- thread_cache_size

-- Check buffer pool hit rate: SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- Calculate hit rate: -- Hit rate = (Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads) -- Should be > 99%

-- Check query cache (MySQL 5.7): SHOW STATUS LIKE 'Qcache%';

-- Disable query cache (MySQL 8.0+ removed): SET GLOBAL query_cache_type = OFF; ```

Step 8: Check Table Statistics

```sql -- Analyze tables: ANALYZE TABLE orders, customers, products;

-- Check table size: SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'mydb' ORDER BY DATA_LENGTH DESC;

-- Check fragmentation: SELECT TABLE_NAME, DATA_FREE FROM INFORMATION_SCHEMA.TABLES WHERE DATA_FREE > 0;

-- Optimize table: OPTIMIZE TABLE orders;

-- Check index cardinality: SHOW INDEX FROM orders;

-- Update statistics: ANALYZE TABLE orders; ```

Step 9: Use Query Profiling

```sql -- Enable profiling: SET profiling = 1;

-- Run query: SELECT * FROM orders WHERE customer_id = 123;

-- Show profile: SHOW PROFILE;

-- Show detailed profile: SHOW PROFILE ALL FOR QUERY 1;

-- Show CPU/Memory: SHOW PROFILE CPU, MEMORY FOR QUERY 1;

-- List all profiles: SHOW PROFILES;

-- Check status of query: SHOW STATUS LIKE 'Handler%'; SHOW STATUS LIKE 'Created_tmp%'; ```

Step 10: MySQL Slow Query Verification Script

```bash # Create verification script: cat << 'EOF' > /usr/local/bin/check-mysql-slow.sh #!/bin/bash

echo "=== Slow Query Status ===" mysql -e "SHOW VARIABLES LIKE 'slow_query%'" 2>/dev/null

echo "" echo "=== Long Query Time ===" mysql -e "SHOW VARIABLES LIKE 'long_query_time'" 2>/dev/null

echo "" echo "=== Top 10 Slow Queries ===" mysql -e "SELECT query_time, sql_text FROM mysql.slow_log ORDER BY query_time DESC LIMIT 10" 2>/dev/null

echo "" echo "=== Running Long Queries ===" mysql -e "SELECT ID, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 5" 2>/dev/null

echo "" echo "=== Table Sizes ===" mysql -e "SELECT TABLE_NAME, TABLE_ROWS, ROUND(DATA_LENGTH/1024/1024) AS 'Data_MB', ROUND(INDEX_LENGTH/1024/1024) AS 'Index_MB' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema') ORDER BY DATA_LENGTH DESC LIMIT 10" 2>/dev/null

echo "" echo "=== Buffer Pool Hit Rate ===" mysql -e "SELECT ROUND(100 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests * 100), 2) AS 'Hit_Rate_%' FROM (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_reads FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') r, (SELECT VARIABLE_VALUE AS Innodb_buffer_pool_read_requests FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests') rr" 2>/dev/null

echo "" echo "=== Index Usage Statistics ===" mysql -e "SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT_READ, COUNT_FETCH FROM PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE ORDER BY COUNT_READ DESC LIMIT 10" 2>/dev/null EOF

chmod +x /usr/local/bin/check-mysql-slow.sh

# Usage: /usr/local/bin/check-mysql-slow.sh

# Monitor in real-time: watch -n 10 /usr/local/bin/check-mysql-slow.sh ```

MySQL Query Checklist

Verify the Fix

```sql -- After optimizing query

-- 1. Check execution plan EXPLAIN SELECT * FROM orders WHERE customer_id = 123; -- type: ref, key: idx_customer_id

-- 2. Test query speed SELECT BENCHMARK(1000, (SELECT id FROM orders WHERE customer_id = 123 LIMIT 1)); -- Fast

-- 3. Check slow query log -- No new entries for optimized query

-- 4. Monitor buffer pool -- High hit rate

-- 5. Compare before/after -- Query time reduced significantly

-- 6. Monitor over time -- Consistent performance ```

Related Issues

• [Fix MySQL Connection Timeout](/articles/fix-mysql-connection-timeout)
• [Fix MySQL Authentication Failed](/articles/fix-mysql-authentication-failed)
• [Fix MySQL Slow Query](/articles/fix-mysql-slow-query)