How to Detect Slow Queries

Slow queries, as the name suggests, refer to inefficient queries that take an excessively long time to execute in a database.

Queries that were not problematic when data was scarce can become slow as the service grows and data accumulates.

This can also happen due to reasons such as a lack of indexing or inefficient data scanning.

They excessively occupy database, CPU, and memory resources, and hold connections between the application and the database for extended periods, ultimately leading to service-wide paralysis or delays.

Threshold criteria: Typically, queries taking more than a few seconds can be defined as slow, but this is relative; if 0.5 seconds is considered heavy for a service, then that would be a slow query.
Main causes: Occur due to a lack of appropriate indexes, full table scans, joining unnecessary data, or inefficient sorting.

Two Main Approaches to Detecting Slow Queries #

Methods for finding slow queries are broadly divided into two categories: utilizing the basic logging features provided by the database itself, and using external monitoring tools.

DB's Built-in Slow Query Log #

Most RDBMSs like MySQL, PostgreSQL, and Oracle provide a feature to log queries that exceed a certain execution time threshold into a separate file or table.

Pros: Measurement is done by the DB engine itself, making it the most accurate, and there are no additional tool adoption costs.
Cons: Requires direct analysis of log files, which can reduce readability, and management can be cumbersome when operating multiple databases.

Utilizing APM (Application Performance Monitoring) Tools #

Application monitoring tools like Datadog, New Relic, Pinpoint, and Scouter allow visual tracking of which API paths and queries are taking a long time.

Pros: Provides intuitive visibility through web dashboards, making it easy to understand the context of which user request triggered a slow query.
Cons: Paid tools incur costs, and initial setup like agent installation is required.

In summary, the DB's built-in features involve the DB itself as the analysis subject, recording information in text log files or DB tables to focus on the query's own details and context. This is used for periodic batch analysis or detailed DBA tuning.

APM monitoring tools use external monitoring agent dashboards, allowing for the understanding of visualized graphs or timeline traces, and making it easy to grasp the overall execution flow of requests at the API level. They aid in real-time fault detection and quick root cause analysis for developers.

Example Data and Result Set (Commands, Configuration, Analysis) #

Let's explore the process of analyzing slow queries using MySQL, one of the most widely used relational databases, as an example.

Slow Query Log Configuration `my.cnf` or `mysqld.cnf` #

**[mysqld]
# 슬로우 쿼리 로그 활성화 (1: On, 0: Off)
slow_query_log = 1

# 로그 파일이 저장될 경로 지정
slow_query_log_file = /var/log/mysql/mysql-slow.log

# 이 시간(초)을 초과하는 쿼리만 기록 (예: 2초 이상 걸리면 기록)
long_query_time = 2.0

# 인덱스를 타지 않는 쿼리는 시간이 짧아도 무조건 기록 (선택 사항)
log_queries_not_using_indexes = 1**

If a query exceeding the specified time (2.0) is executed as above, information like the following will accumulate in the file at the /var/log/mysql/mysql-slow.log path.

# 터미널에서 실시간 로그 확인
tail -f /var/log/mysql/mysql-slow.log

# Time: 2026-03-15T14:31:00.123456Z
# User@Host: app_user[app_user] @  [192.168.1.50]  Id: 100
# Query_time: 3.510234  Lock_time: 0.000100 Rows_sent: 10  Rows_examined: 1500000
SET timestamp=1646092000;
SELECT * FROM user_logs WHERE action = 'login' ORDER BY created_at DESC LIMIT 10;

The query SELECT * FROM user_logs WHERE action = 'login' ... requested from the 192.168.1.50 server took 3.51 seconds to execute. It shows an inefficiency where only 10 rows (Rows_sent) were sent to the client, but the database internally scanned 1.5 million rows (Rows_examined) to find them.

Checking the Execution Plan for Root Cause Analysis (Explain) #

Once the problematic query is identified through logs, you should prepend EXPLAIN to the query in a DB terminal or tool to check how the database executes it.

EXPLAIN SELECT * FROM user_logs WHERE action = 'login' ORDER BY created_at DESC LIMIT 10;

The output example will appear as a table:

id,select_type,table,type,possible_keys,key,rows,Extra
--------------------------------------------------------
1,SIMPLE,user_logs,ALL,NULL,NULL,1500000,Using filesort

In summary, type being ALL and key being NULL means that an index is not being used, and the entire table is being scanned.

Furthermore, the presence of Using filesort in Extra indicates that a heavy sorting operation was performed in memory or on disk, meaning that adding an index is urgently needed.

To resolve such detected slow queries, one can proceed with approaches like adding indexes or improving the query structure.