What is the slow query log in MySQL?
Why Interviewers Ask This
Mid-level MySQL / SQL roles require deep understanding of this topic. Interviewers ask this to separate candidates who truly understand the mechanics from those who only know surface-level concepts.
Answer
The slow query log records SQL queries that take longer than a configurable threshold to execute, helping identify performance bottlenecks. Enable it: slow_query_log = ON, slow_query_log_file = /var/log/mysql/slow.log, long_query_time = 1 (log queries taking more than 1 second — adjust to your needs; use 0.1 for busy systems). Also log queries that don't use indexes: log_queries_not_using_indexes = ON (use carefully — can log many queries). You can enable/disable at runtime: SET GLOBAL slow_query_log = ON;. Reading the slow query log: use mysqldumpslow (built-in) to summarize: mysqldumpslow -s t -t 10 /var/log/mysql/slow.log (top 10 by total time). Or use pt-query-digest (Percona Toolkit) for richer analysis including query fingerprinting, execution count, and percentile timing. Workflow: enable slow log → let it collect for a period → analyze with pt-query-digest → take the worst queries → EXPLAIN them → add indexes or rewrite queries → verify improvement.
Pro Tip
If you're unsure about a detail, say so honestly and explain your reasoning. Interviewers respect candidates who can think through uncertainty rather than bluffing.