Slow Query Logs — Complete Guide
Slow Query Logs — Complete Guide: free step-by-step lesson with examples, common mistakes, and interview tips — part of MySQL Tutorial on Toolliyo Academy.
On this page
MySQL Tutorial · Lesson 69 of 100
Slow Query Logs
Basics ✓ → Advanced
Advanced · 2 — Production · ~10 min · MySQL — Indexing & Performance
What is this?
Slow query log records statements exceeding long_query_time seconds. Turn it on in dev/staging to find bad SQL before users complain.
Why should you care?
One missing index shows up as thousands of 2-second queries in log — fix once, save daily.
See it live — copy this example
Run in MySQL Workbench or the mysql CLI.
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_output = 'TABLE';
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 5;What happened?
- long_query_time 0.5 logs queries over half second.
- log_output TABLE writes to mysql.slow_log queryable via SQL.
- Production often uses FILE + pt-query-digest.
Practice next
- Enable slow log in local my.cnf.
- Run intentional full scan SELECT.
- Read mysql.slow_log or slow.log file.
- log_queries_not_using_indexes = ON in dev — noisy but educational.
- Use pt-query-digest on slow.log if installed.
Remember
Logs queries above time threshold. TABLE or FILE output options. Feed logs to digest tools for patterns.
DataFlow weekly DBA review
Top 10 slow patterns from log drive index sprint every Monday.
Outcome: Regression caught when deploy adds bad JOIN.
Interview prep for this lesson
Practice these questions aloud after reading—each links to a full structured answer.
Sign in to ask a question or upvote helpful answers.
No questions yet — be the first to ask!
