Server Slow Queries view — MySQL

Individual statements that crossed the slow threshold, and the numbers that explain why.

MySQL's slow query log records every statement slower than long_query_time, with its lock wait, rows sent, and rows examined. This view reads that log, from the file or the mysql.slow_log table, as a searchable list.

Query Digest shows which patterns cost the most overall. This view shows actual statements with actual values, so you can run one yourself and see what it does.

Where the Log Comes From #

The server's Log Output setting decides where entries go, and so how Querious reads them.

`log_output`	How Querious reads it
TABLE	From `mysql.slow_log` over your existing connection. Nothing else to set up.
FILE	From `slow_query_log_file`, directly for a server on this Mac or over SSH for a remote one. See Server Access.
FILE,TABLE	Both are written; either works.

If slow query logging is off, the view says so. Open… loads a log from elsewhere (rotated, or copied from another machine), and Save as… writes what's loaded out, including as CSV.

The Configuration Section #

The settings the log was produced under are shown alongside it, since a slow log is meaningless without knowing what "slow" was.

Setting	Meaning
Log Slow Queries	Whether `slow_query_log` is on.
Log Output	File, table, or both.
Long Query Time	The threshold in seconds. Statements exceeding it are logged.
Log Not Using Indexes	Log statements that run without an index regardless of speed. Often the more valuable of the two.
Min Rows Examined	`min_examined_row_limit`: statements examining fewer rows aren't logged even if slow.
Log File Path	Where the file lives.
Server ID	Which server produced the entries. Matters for a log copied from elsewhere.

Change any of these in Variables. Most take effect immediately, and all revert on restart unless also in my.cnf.

Reading Entries #

Column	What it shows
Time	How long the statement took.
Timestamp	When it ran.
User, Host	Who ran it and from where.
Database	The schema it ran against.
Query	The full statement with literal values.

Selecting an entry shows Rows Sent, Rows Examined, Lock Time, and insert IDs.

Detail	What to make of it
Rows Examined vs Rows Sent	The most useful ratio in the log. A million examined to send ten is a missing or unusable index.
Lock Time	The time spent waiting for locks rather than working. A statement that's nearly all lock time isn't slow; something else held it up. See Locks.

Combine Similar Queries #

The Combine Similar Queries checkbox (on by default) groups entries that are the same statement with different literal values into one row, averaging their numbers. Leave it on to see which kind of query dominates, and turn it off to get back to individual statements you can copy and run.

Recipes #

To start catching slow queries

In Variables, set slow_query_log to ON.
Set long_query_time to something meaningful for your application.

One second is common; on a fast OLTP system, 0.1 finds far more.
Turn on log_queries_not_using_indexes.

This catches statements that are fast today only because the table is small.
Set log_output to TABLE or FILE.

TABLE avoids arranging file access. FILE gives a log that survives independently of the server.
Add the same settings to my.cnf so they survive a restart.

To find the query worth fixing first

Turn on Combine Similar Queries and sort by Time descending.
Compare Rows Examined with Rows Sent for the top entries.

A large ratio is an indexing problem and usually the cheapest to fix.
Turn combining off, find a real instance, copy it, and run it with EXPLAIN in the Query view.

To tell a slow query from a blocked one

Compare Lock Time with Time.

If lock time is most of the total, the statement was fine and waited on someone else.
Look at what else was running at that timestamp: a batch job, a migration, a long transaction.
Open Locks while the problem is happening to catch the blocker.