MySQL Slow Query Log: Finding Costly Queries

When your game server or web app starts dragging, the culprit is usually a handful of expensive SQL queries, and spotting them by eye is impossible. This is exactly where the MySQL slow query log earns its keep: it quietly records every query that runs longer than a threshold you set, so you can look at data instead of guessing. In this article I walk through enabling the log correctly, choosing the right settings, and systematically extracting your most costly queries.

What the slow query log actually is

The slow query log is a MySQL feature that records any SQL statement running longer than long_query_time seconds. "Slow" is relative: a 0.5-second character lookup on a Metin2 server is a disaster, while 2 seconds on a reporting screen may be fine. Each line stores query time, lock time, rows examined, and rows sent. The real power is in that metadata: very often the problem isn't the query itself but that it scans millions of rows just to return ten.

Enabling the log: persistent configuration

The most robust approach is to write to my.cnf (on Debian/Ubuntu usually /etc/mysql/mysql.conf.d/mysqld.cnf). Add these under the [mysqld] block:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

Then restart the service: sudo systemctl restart mysql. Here's what each option does:

• long_query_time — the threshold in seconds. It accepts decimals, so you can write 0.5.
• log_queries_not_using_indexes — logs every query that doesn't use an index, even fast ones. Invaluable in development, but it can produce a lot of noise.
• min_examined_row_limit — ignores queries that examine fewer than this many rows, keeping small-table chatter out of the log.

Enabling it live, without a restart

If you can't restart a production server, MySQL lets you change most of these variables at runtime. Connect as root and run:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

One caveat: long_query_time is read per session, so only new connections will pick up the value; your existing connection pool keeps using the old threshold. Also, changes made with SET GLOBAL are lost on restart — for persistence you still need my.cnf.

Extracting the costliest queries: pt-query-digest

Reading the raw log by hand is torture; the same query repeats hundreds of times. Percona Toolkit's pt-query-digest normalizes those lines (replacing literal values with ?) and groups identical queries under a single "fingerprint." Install it on Debian/Ubuntu:

sudo apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log > report.txt

The report ranks queries by total time spent at the top — a query that takes 50 ms but runs 200 times a second matters just as much as one that takes 5 seconds once. For each group you get the call count, total and average time, and a sample query. Usually the top three queries account for most of the load; focus there instead of optimizing blindly.

Reading a query: EXPLAIN and indexes

Once you've found the culprit, prefix it with EXPLAIN to see MySQL's plan:

EXPLAIN SELECT * FROM player WHERE account_id = 4210 ORDER BY level DESC;

The fields to watch in the output:

• type — if you see ALL, it's a full table scan, which is bad; you want ref or range.
• key — if it's NULL, no index is being used at all.
• rows — the estimated number of rows MySQL plans to scan; if that's far larger than your result set, the problem lives here.
• Extra — Using filesort or Using temporary means extra cost for sorting or grouping.

In the example above, adding an index on account_id collapses the scan: CREATE INDEX idx_player_account ON player (account_id);. For columns frequently filtered together, consider a composite index — but every unnecessary index adds write cost, so measure before you add.

Keeping the log under control

The slow query log grows over time; in production you must rotate it. Most distributions ship /etc/logrotate.d/mysql-server, and if not, a simple rule does the job. When diagnosis is done, turn off log_queries_not_using_indexes; this option can fill the file with small but index-less queries. On busy servers where disk I/O is precious, enable the log only while investigating, and keep long_query_time at a sensible threshold (such as 1) the rest of the time.

Frequently Asked Questions

Does the slow query log slow down performance?

With a reasonable threshold the impact is negligible, because only queries exceeding it are written. The real cost comes with log_queries_not_using_indexes enabled: on a high-traffic server that can quickly inflate the log file and disk writes. Keep it off outside of diagnosis.

What should I set long_query_time to?

Start with 1 second. If nothing is captured, lower it gradually (0.5, then 0.2). A value that's too low fills the log with noise; the goal is to see the costliest queries, not every query.

Can I log to a table instead of a file?

Yes, with log_output = 'TABLE' entries go to the mysql.slow_log table and can be queried with SQL. But file output is far more practical with pt-query-digest, so a file is preferable in most cases.

If your server is slowing down and you don't know where to start, we can enable the slow query log and interpret your first pt-query-digest report together. For a MySQL performance audit and query optimization, get in touch with me.