How to view and optimize MySQL slow log?

To view and optimize MySQL slow logs, follow these steps:

1. Enable Slow Query Log

First, ensure the slow query log is enabled in MySQL. Check the configuration in my.cnf or my.ini:

slow_query_log = 1  
slow_query_log_file = /var/log/mysql/mysql-slow.log  
long_query_time = 2  # Queries taking longer than 2 seconds are logged  
log_queries_not_using_indexes = 1  # Logs queries without indexes  

Restart MySQL to apply changes:

sudo systemctl restart mysql  

2. View Slow Log

The slow log is a text file (e.g., /var/log/mysql/mysql-slow.log). Use cat, less, or tail to view it:

sudo less /var/log/mysql/mysql-slow.log  

Alternatively, use mysqldumpslow to summarize slow queries:

mysqldumpslow -s t /var/log/mysql/mysql-slow.log  # Sort by query time  

3. Optimize Slow Queries

• Identify problematic queries: Look for high Query_time, Rows_examined, or Rows_sent.
• Add indexes: If queries scan many rows (Rows_examined >> Rows_sent), add appropriate indexes.
• Rewrite queries: Avoid SELECT *, use EXPLAIN to analyze query execution plans.
• Optimize server settings: Adjust innodb_buffer_pool_size, query_cache_size (if used).

4. Use MySQL Performance Schema (Optional)

Enable Performance Schema for deeper insights:

SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC LIMIT 10;  

5. Tencent Cloud Recommendations

For managed MySQL optimization, use TencentDB for MySQL, which provides:

• Slow query analysis in the console.
• Automatic indexing suggestions via Database Intelligence.
• Performance monitoring with Cloud Monitor.

Example: In TencentDB for MySQL, navigate to Slow Query Analysis in the console to view detailed reports and recommendations.