# Enabling the slow query log in MySQL

> Turn on the MySQL slow query log on a server with root access to find queries that take too long and slow down your site.

Source: https://helpcenter.ruachost.com/developers/databases/enabling-the-slow-query-log-in-mysql/

This guide explains how to activate the MySQL slow query log, which helps you identify queries that take unusually long to run and may be affecting your database or server performance.

**Important**\
Root access is required to complete these steps. If you are on a Managed VPS or Managed Dedicated Server without root access and need the slow query log enabled, open a support ticket at [https://dashboard.ruachost.com](https://dashboard.ruachost.com/submitticket.php?step=2&deptid=3).

**Note**\
Details about unmanaged dedicated servers apply only to customers who purchased those plans before May 27, 2024, as these plans are no longer offered.

**Why Use the Slow Query Log**\
The slow query log records any database query that exceeds a specified execution time. This makes it easier to detect inefficient queries that may slow down your system.

## How to Enable the Slow Query Log

1. Log in to your server through SSH.
2. At the command prompt, enter:

   ```
   mysql -u root -p
   ```
3. Enter the MySQL root password.
4. At the `mysql>` prompt, turn on the slow query log:

   ```
   SET GLOBAL slow_query_log = 'ON';
   ```

## Adjusting Slow Query Log Settings

- **Change the time threshold:**\
  By default, MySQL logs queries running longer than 10 seconds. To set a different threshold (replace X with seconds):

  ```
  SET GLOBAL long_query_time = X;
  ```
- **Change the log file location:**\
  The default log file is `/var/lib/mysql/hostname-slow.log`. To set a custom path and filename:

  ```
  SET GLOBAL slow_query_log_file = '/path/filename';
  ```

## Testing the Slow Query Log

1. Exit MySQL and log in again to refresh session variables.
2. Run a test query that waits longer than the configured `long_query_time` (replace X with a value above your threshold):

   ```
   SELECT SLEEP(X);
   ```
3. Check the slow query log file to confirm that the query was recorded.

## After Troubleshooting

Disable the slow query log when you no longer need it:

```
SET GLOBAL slow_query_log = 'OFF';
```

**Important**\
The slow query log should only remain active while diagnosing performance issues.

**Additional Resources**\
For more details about the MySQL slow query log, visit the official MySQL documentation: <https://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html>