Slow MariaDB SQL queries in Statistics state

Overview

One of the jobs of a database when executing a query is to map out the best way to execute the query itself. While in most instances MariaDB (and MySQL) does a great job of optimisation, for some complex queries with a high number of joins it can spend too much time by default trying to optimise the query instead of actually executing it.

For example, on a WordPress based query with 16 JOIN statements, we saw an execution time of nearly 4 minutes with only 5000 rows. Running an EXPLAIN on the query (which doesn’t actually execute the query) results in roughly the same, which means the delay is the over-opimisation not the data or query itself.

This is due to the optimizer_search_depth setting, which defaults to 62. By reducing this number to a depth of 5, the EXPLAIN time was down to 0.052 seconds and the query itself reduced to under 6 seconds.

For this particular query it meant the result was nearly 3900% slower with server defaults!

Instructions

1. Using your MariaDB shell, execute the query and then view what MariaDB is during the query:
   show full processlist;
2. If you see a query with the state of “Statistics” for a long time, this optimisation could be applicable. For example:
   
3. Confirm what your current optimizer_search_depth is set to:
   show variables like "optimizer_search_depth";
   For example, you may therefore see:
   +------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
4. This indicates that your server is still running system defaults.
5. Test setting the server to auto-opimise the depth by setting optimizer_search_depth to zero:
   SET SESSION optimizer_search_depth = 0;
6. Re-execute the slow SQL query to confirm it’s resolved the issue.
7. If the updated setting has worked, set it permanently by editing the /etc/my.conf and explicitly setting in the [msqld] section:
   optimizer_search_depth=0
8. Restart MariaDB to apply:
   systemctl restart mariadb

If you are a Conetix customer with a Virtual Private Server having this issue, we can help confirm this is the cause and fix it for you at no additional cost. Please contact our support team for further assistance.

Further Reading

https://www.percona.com/blog/2012/04/20/joining-many-tables-in-mysql-optimizer_search_depth/

https://mariadb.com/resources/blog/setting-optimizer-search-depth-in-mysql/