Slow Query Localization and Index Optimization: Complete Steps from Slow Logs to EXPLAIN

When a website slows down and the issue is traced back to the database, the next step is to identify which SQL statement is slow and why. This process follows a standard pattern: enable slow query logs → locate slow statements → analyze with EXPLAIN → add indexes or rewrite queries.

The most error-prone step is the third one—many people add indexes only to find they are not being used, because the query syntax causes index invalidation.

Step 1: Enable Slow Query Logs

# Check current configuration
mysql -e "SHOW VARIABLES LIKE 'slow_query%';"
mysql -e "SHOW VARIABLES LIKE 'long_query_time';"

# /etc/my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2        # Record queries taking longer than 2 seconds
log_queries_not_using_indexes = 1   # Optional: Log queries that do not use indexes

systemctl restart mysqld

Setting long_query_time requires balance: if set too high, you won’t catch issues; if set too low, the log will grow excessively. Start with 2 seconds and adjust after observation.

Step 2: Analyze Slow Logs

# MySQL built-in tool: Sort by total execution time, get top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Sort by frequency of occurrence
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# More detailed analysis (requires Percona Toolkit)
pt-query-digest /var/log/mysql/slow.log | head -50

Both “high frequency” and “long single execution time” warrant optimization, but with different priorities: high-frequency queries usually offer greater returns because they repeatedly consume resources.

Step 3: Analyze with EXPLAIN

mysql -e "EXPLAIN SELECT * FROM wp_posts WHERE post_status='publish' ORDER BY post_date DESC LIMIT 10G"

The most direct judgment: If type=ALL and rows is large, this is your target for optimization.

Step 4: Add Indexes

# View existing indexes
mysql -e "SHOW INDEX FROM dbname.tablenameG"

# Add an index
mysql -e "ALTER TABLE dbname.tablename ADD INDEX idx_status (post_status);"

# Composite index (note the field order)
mysql -e "ALTER TABLE dbname.tablename ADD INDEX idx_status_date (post_status, post_date);"

# Verify with EXPLAIN after adding
mysql -e "EXPLAIN SELECT ... G"

Field Order in Composite Indexes

Follow the Leftmost Prefix Principle: An index on (a, b, c) can serve queries for a, (a,b), and (a,b,c), but cannot directly serve queries for b or c alone.

-- Index (post_status, post_date)
-- ✓ Usable: WHERE post_status='publish'
-- ✓ Usable: WHERE post_status='publish' AND post_date > '2026-01-01'
-- ✗ Not usable: WHERE post_date > '2026-01-01'   -- Skips the leftmost field

Therefore, when creating composite indexes, place the fields most frequently used for filtering on the left.

Common Patterns That Invalidate Indexes

“Applying functions to columns” is the most隐蔽 (hidden) case—the syntax looks natural, but it directly invalidates the index. DATE(created)=... may look more elegant than range queries, but the cost is a full table scan.

Verify Optimization Results

# Record execution time before optimization
mysql -e "SET profiling=1; SELECT ...; SHOW PROFILES;"

# Or compare rows using EXPLAIN
# Then check if the statement still appears in slow logs
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

After adding an index, confirm it is actually being used. If the key field in EXPLAIN is still NULL, it means the index is not生效 (effective)—in this case, recheck for the invalid patterns mentioned above.

Decision Tree

Optimize Slow Queries
├─ Enable slow logs, locate slow statements
├─ Analyze with EXPLAIN
│  ├─ type=ALL + large rows   → Needs index
│  └─ Extra has Using filesort → Consider adding sort fields to index
├─ Add indexes (note composite index field order)
├─ Re-run EXPLAIN to confirm key is not NULL
│  └─ Still NULL → Check for invalid patterns:
│     ├─ Functions applied to columns
│     ├─ Type mismatch
│     ├─ Leading % in LIKE
│     └─ OR conditions
└─ Verify: Does the statement disappear from slow logs?

Scenario Walkthrough: Adding an Index Makes Slow Queries Slower

Phenomenon: After a core query was identified as a slow query, relevant personnel added indexes to the involved fields.
After deployment, this specific statement indeed became faster, but overall write performance across the database significantly degraded, leading to new queuing issues during peak hours.

1. First, confirm if the slow query actually improved → Execute the statement individually;
   the execution time has indeed decreased. This indicates the index choice was correct, and the problem lies elsewhere.
2. Realize that indexes come with costs → Every write operation must synchronously maintain index structures;
   the more indexes there are, the heavier the write burden.
3. Check for duplicate or redundant indexes → Multiple indexes starting with the same fields exist,
   some of which are never used.
4. Confirm optimization direction: Reduce indexes instead of adding more → Clean up unused indexes,
   which does not affect queries while reducing write overhead.
5. Verify via execution plan that queries still use indexes → After cleanup, re-check manually,
   ensuring the necessary indexes remain.
6. Execute structural changes during off-peak hours → Large table alterations lock tables;
   timing selection directly impacts business operations.

# First, check for redundant indexes (starting with the same field)
mysql -e "SHOW INDEX FROM tablenameG" | grep -E 'Key_name|Seq_in_index|Column_name'

# Identify indexes that have never been used
mysql -e "SELECT object_schema, object_name, index_name
          FROM performance_schema.table_io_waits_summary_by_index_usage
          WHERE index_name IS NOT NULL AND count_star = 0
            AND object_schema = 'dbname'G" 2>/dev/null

# Use execution plans before and after cleanup (check for full table scans)
mysql -e "EXPLAIN SELECT ... G"

# Schedule large table changes during off-peak hours, and verify table size first
mysql -e "SELECT table_name, ROUND(data_length/1024/1024) MB FROM information_schema.tables
          WHERE table_schema='dbname' ORDER BY data_length DESC LIMIT 5;"

Root Cause: Only query performance was considered, ignoring the write maintenance cost of indexes,
and the table contained multiple unused redundant indexes. The optimization direction should be “reduction” rather than “addition”.

Two Key Takeaways:

• More indexes are not always better: each one increases maintenance overhead during writes.
  Tables with few writes and many reads, and those with frequent writes, should adopt opposite indexing strategies.
• Before optimizing, check for redundant and unused indexes.
  Cleaning them up is often safer than adding new ones, with more direct benefits.

8 Common Pitfalls

1. Adding indexes without verification: They might not be used at all.
2. Applying functions to columns: Directly invalidates indexes.
3. Type mismatch: Querying integer columns with strings.
4. Leading % in LIKE: Prevents index usage.
5. Incorrect composite index order: Violates the leftmost prefix principle.
6. Setting long_query_time too high: Fails to capture actual issues.
7. Creating too many indexes: Impacts write performance and consumes space.
8. Optimizing based on guesses without checking slow logs: You might be fixing the wrong bottleneck.

Real Customer Feedback (From Cloud Market Reviews and Consultations for Similar Products)

The following excerpts are taken from real buyer reviews and consultations for similar O&M services on Alibaba Cloud / Tencent Cloud / Huawei Cloud marketplaces (service providers include our store and peers, provided solely for reference of real feedback, not fictitious tickets from this site):

1****4 Rating: 5.0 2023-05-08 11:53 1****3 Rating: 5.0 2022-09-22 22:01 r****u Rating: 5.0 2020-07-07 15:05

b****4 Rating: 5.0 2025-06-23 16:57 b****4 Rating: 5.0 2025-06-23 16:56 Jian****Wei Rating: 5.0 2021-01-20 15:03 Technical skills are awesome, speed is fast! It turned out to be a Trojan causing Baidu global hijacking!!!!!!1 f****u Rating: 5.0 2021-01-02 20:43 Thanks for assisting in maintaining Yuncai’s normal operation. 1****1 Rating: 5.0 2020-11-20 10:03 Consistently good service as always. 1****1 Rating: 5.0 2020-11-17 09:13 Thank you for the consistently good service. 1****1 Rating: 5.0 2020-09-25 15:42 Many thanks. 1****1 Rating: 5.0 2020-08-24 15:34 Sincere thanks to your company’s technical staff. 1****1 Rating: 5.0 2020-07-24 09:49 Many thanks!

d****9 Rating: 5.0 2023-02-15 09:11 Yong****Ying Rating: 5.0 2021-03-29 11:04