Skip to main content

How to Identify a Slow MySQL Query

Identify and isolate slow-running MySQL or MariaDB queries to improve website performance and database responsiveness. This process is available to any customer with access to their database via phpMyAdmin or a compatible MySQL client.

Identify and isolate slow-running MySQL or MariaDB queries to improve website performance and database responsiveness. This process is available to any customer with access to their database via phpMyAdmin or a compatible MySQL client.

Before you begin

You will need:

  • Access to your database via phpMyAdmin or a MySQL client (such as MySQL Workbench).

  • A basic understanding of how your application interacts with the database.

Identify slow queries using the Slow Query Log

The most effective way to identify problematic queries is by enabling the MySQL Slow Query Log. This log records any query that takes longer than a specified amount of time to execute.

  1. Access your server's configuration file (typically my.cnf or my.cnf.d/mysql.conf.d).

  2. Locate the [mysqld] section.

  3. Set the following parameters:

    • slow_query_log = 1

    • long_query_time = 2 (This sets the threshold to 2 seconds; you can adjust this number based on your needs).

  4. Restart the MySQL service to apply the changes.

Note: The long_query_time value is in seconds. If you want to catch queries taking longer than 5 seconds, set the value to 5.

Analyze the Slow Query Log

Once the log is active, you can examine the entries to find the specific queries causing delays.

  1. Locate the slow query log file on your server (the path is often defined by the slow_query_log_path variable).

  2. Open the log file and look for queries with high execution times.

  3. Identify patterns, such as queries missing indexes or those performing full table scans.

Use the EXPLAIN statement

If you identify a specific query that is running slowly, use the EXPLAIN statement to see how MySQL executes that query.

  1. Open your query in phpMyAdmin or your preferred SQL client.

  2. Prefix the query with EXPLAIN.

  3. Review the output:

    • type: Look for "ALL", which indicates a full table scan.

    • rows: Check if the number of rows scanned is significantly higher than the number of rows returned.

    • Extra: Look for "Using filesort" or "Using temporary," which can indicate inefficient sorting or grouping.

Identify slow queries via MySQL Workbench

If you use MySQL Workbench, you can use the built-in Performance Reports to find slow queries.

  1. Open MySQL Workbench and connect to your database.

  2. In the Navigator pane, select Performance Reports.

  3. Select Statement Analysis or Slow Queries.

  4. Review the list of queries and sort by Average Latency or Total Latency to find the most impactful queries.

Confirm the result

  • Identify the specific SQL statement(s) causing performance degradation.

  • Observe a reduction in "Time to First Byte" (TTFB) or page load times after optimizing the identified queries.

If you cannot identify the specific query

If you notice a slowdown but cannot find a specific query in the logs, it may be due to a high volume of concurrent connections rather than a single slow query.

  1. Check the number of active connections in your control panel or via the SHOW PROCESSLIST; command.

  2. If the number of connections is consistently at your limit, you may need to optimize your application's connection handling or increase the max_connections limit in your MySQL configuration.

Limitations

  • Enabling the Slow Query Log can slightly increase the load on the database server.

  • The EXPLAIN command provides a plan for how the query will run; it does not execute the query itself.

  • Optimization of a query (such as adding an index) may require a brief period of table locking depending on the size of the table.

Did this answer your question?