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.
Access your server's configuration file (typically
my.cnformy.cnf.d/mysql.conf.d).Locate the
[mysqld]section.Set the following parameters:
slow_query_log = 1long_query_time = 2(This sets the threshold to 2 seconds; you can adjust this number based on your needs).
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.
Locate the slow query log file on your server (the path is often defined by the
slow_query_log_pathvariable).Open the log file and look for queries with high execution times.
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.
Open your query in phpMyAdmin or your preferred SQL client.
Prefix the query with
EXPLAIN.Example:
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
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.
Open MySQL Workbench and connect to your database.
In the Navigator pane, select Performance Reports.
Select Statement Analysis or Slow Queries.
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.
Check the number of active connections in your control panel or via the
SHOW PROCESSLIST;command.If the number of connections is consistently at your limit, you may need to optimize your application's connection handling or increase the
max_connectionslimit in your MySQL configuration.
Limitations
Enabling the Slow Query Log can slightly increase the load on the database server.
The
EXPLAINcommand 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.
