Find and optimize slow SQL queries for XenForo 2.3

Find and optimize slow SQL queries for XenForo 2.3​

As your forum grows, do some pages suddenly load very slowly? Have you optimized images and CSS/JS but still see no improvement? Most likely, the culprit is hiding in the database as one or more inefficient SQL queries. This is especially common with third-party add-ons.
Finding and optimizing these "slow queries" is an advanced skill, but it can make a huge difference in performance. This article will guide you through the step-by-step process to diagnose and fix them.

Step 1: Turn on debug mode to "hunt" Query

To find slow queries, you first need to enable XenForo's debug mode.

1. Using FTP or File Manager, open the file src/config.php in the forum root directory.
2. Add the following line at the end of the file: $config['debug'] = true;
3. Save the file.

Now, when you visit the forum with an admin account, you will see a black debugging toolbar at the bottom of the page.

Step 2: Identify the "culprit" query

1. Visit the page/function on the forum where you feel it is slow.
2. Look at the debug bar below, find and click the item DB.
3. A pop-up window will appear, listing all SQL queries that have been executed to load this page, along with the execution time of each query.
4. Look for queries with unusually high execution times (for example, over 0.5 seconds). These are the "slow queries" we need to find.
5. Copy the entire SQL code of that query.

Step 3: Diagnose with command EXPLAIN

EXPLAIN as a MySQL command, it will tell us the "plan" that the database will use to execute a query.

1. Login phpMyAdmin (or another database management tool).
2. Select the forum database and open the tab SQL.
3. Type keywords EXPLAIN at the top, followed by the slow SQL code you copied. For example:
   
   SQL:

   EXPLAIN SELECT * FROM `xf_post` WHERE `message_state` = 'visible' ORDER BY `post_date` DESC LIMIT 10;
4. Click "Go".

The returned result will be an analysis table. Pay attention to the 3 most important columns:

• type: This is the most important column. If it has value is ALL, this is an extremely bad sign, which means MySQL has to scan the entire table to search. Good value is usually ref or index.
• key: Indicates which index is being used. If is NULL, means there is no index used for this query.
• rows: Estimate the number of lines that MySQL must scan. If type is ALL and rows a huge number, you've figured out the problem.

Step 4: Solution - Add index

If a query has to scan the entire table (type: ALL) since it is filtering/sorting by a column without an index, the solution is to add an index to that column.

• What is Index? Imagine it as the table of contents of a book. Instead of reading from start to finish to find a chapter, just look at the table of contents. Database index works similarly.
• How to add Index in phpMyAdmin:

1. Determine the column that needs to add index. Usually columns in clauses WHERE or ORDER BY of slow query.
2. Select the corresponding table (for example: xf_post).
3. Switch to tab Structure.
4. Look down at the section Indexes, click Add index.
5. In the window that appears, name the Index and select the column(s) you want to add to the index.
6. Click "Go".

• After adding the index, run the command again EXPLAIN in Step 3. You'll see the values type, key and rows significantly improved.

Conclusion

Analyzing and optimizing slow queries is an advanced but extremely powerful skill. The procedure is straightforward: enable debug → find the slow query → analyze it with EXPLAIN → add an index → check again.
Final advice: If the slow query comes from a third-party add-on, it's best to copy the EXPLAIN analytics output and send a report to the developer of that add-on. They are the ones who should repair and optimize it. And don't forget to turn off debug mode after completing the job!