# Identifying Slow Queries
Slow queries can degrade the performance of your MySQL-based application, leading to lag, timeouts, or higher resource consumption. On Elestio, whether you’re accessing MySQL via terminal, inside a Docker Compose container, or using MySQL CLI tools, there are structured ways to inspect and optimize query performance. This guide covers how to analyze slow queries, interpret execution plans, and apply performance improvements using techniques like EXPLAIN, slow query logs, and schema analysis.
## **Analyzing Slow Queries from the Terminal**
When connected to a MySQL server from a terminal, you can use native SQL statements and built-in features to analyze the performance of specific queries. This is ideal for diagnosing issues in staging or production without needing container access.
#### **Connect to your MySQL instance via terminal**
To begin, log in to your MySQL server using the MySQL client:
```
mysql -u -h -p
```
You’ll be prompted for the password. Once inside, you can start analyzing queries.
#### **Use EXPLAIN to view the execution plan**
The EXPLAIN keyword shows how MySQL plans to execute a query. It breaks down how tables are accessed and joined, whether indexes are used, and how many rows are expected to be scanned.
```mysql
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;
```
Review the type, key, rows, and Extra columns in the output. Look out for full table scans (type = ALL), which often signal that an index may be missing.
#### **Check current running queries**
To view which queries are actively running and their duration, use:
```mysql
SHOW FULL PROCESSLIST;
```
This can help identify long-running or stuck queries in real time.
## **Analyzing Inside Docker Compose**
If your MySQL service is running inside a Docker Compose setup (as Elestio uses), it may not be directly exposed on your host. In this case, analysis must be done from within the container.
#### **Access the MySQL container**
Open a shell inside your MySQL container using Docker Compose:
```
docker-compose exec mysql bash
```
This gives you a command-line shell inside the container.
#### **Connect to MySQL from inside the container**
Once inside the container, use the environment-defined credentials to access the database:
```
mysql -u $MYSQL_USER -p$MYSQL_PASSWORD $MYSQL_DATABASE
```
This gives you the same SQL interface as from the host terminal, enabling you to use EXPLAIN, SHOW PROCESSLIST, and performance schema tools.
#### **Enable and view the slow query log**
MySQL can log slow queries to a file. This must be enabled in your container’s `my.cnf` configuration file:
```
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
```
After applying these settings, restart the container. Slow queries taking longer than long\_query\_time (in seconds) will be logged.
You can then inspect the log file:
```
cat /var/log/mysql/slow.log
```
## **Using Performance Schema**
MySQL’s performance schema and built-in commands help track query statistics over time. This is useful when diagnosing repeat offenders or inefficient patterns.
#### **Enable the performance schema (if not already)**
Ensure performance\_schema is enabled in your MySQL configuration:
```
[mysqld]
performance_schema=ON
```
Restart the container after updating the config.
#### **Identify top queries using statement summaries**
This SQL query shows which SQL statements have the longest average execution times:
```mysql
SELECT digest_text, count_star, avg_timer_wait/1000000000000 AS avg_time_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;
```
This helps you find the most resource-intensive queries over time.
## **Understanding the MySQL Execution Plan**
Reading the output of EXPLAIN is essential to understand how MySQL processes your query and whether it is using indexes efficiently.
Key output fields to interpret:
- **type**: The join type. Prefer ref, range, or const over ALL (which indicates a full table scan).
- **key**: The index used for the query. A NULL value may indicate a missing index.
- **rows**: Estimated number of rows MySQL will scan. Lower is better.
- **Extra**: Look for warnings like Using temporary or Using filesort, which may suggest suboptimal queries.
Use `EXPLAIN ANALYZE` (available in MySQL 8.0+) to see actual vs. estimated performance:
```mysql
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
```
## **Optimizing Queries for Better Performance**
Once you’ve identified inefficient queries, optimization involves rewriting queries, adding indexes, or adjusting the schema.
Common techniques:
- **Add indexes** to columns frequently used in WHERE, JOIN, and ORDER BY.
- **Avoid SELECT\*** : Only fetch columns you need to reduce I/O.
- **Use LIMIT** when fetching preview data or paginated results.
- **Re-write joins or subqueries** to reduce temporary tables and filesort operations.
- **Update statistics** with ANALYZE TABLE:
```mysql
ANALYZE TABLE orders;
```