How to fix slow queries

One issue that can cause your website to experience performance problems is slow database queries. Many contributed and custom modules regularly create and submit SQL queries to your database, and any of these queries can affect your website's overall performance.

Examining your website for query performance issues

Checking for slow queries is a good place to start your performance investigation. Although there are many modules that create queries, one particular common source of queries comes from using the Views module.

When you're narrowing down where a problem might be, consider what kind of queries are used most heavily. There are three major types of queries you're likely to be using:

• SELECT queries pull info for display without changing it.
• UPDATE queries alter existing information.
• DELETE queries remove information from the database.

When you're investigating a query, you can look at the list of fields or tables contained in the first part of the query. Try to match these against the names of the fields in your website's nodes, users, and entities. What portion of your website displays data that uses these fields? Frequent culprits will be items like blocks, views, panels, and calendars. You can then try to use that to determine where large groups of data may be coming from, and what tables may need investigating.

If looking at the fields and tables does not allow you to pinpoint where in your website the query may be coming from, compare the number of results returned with various components of your website to see if the number of results match.

If you're unable to determine the source of the query, open a ticket with Acquia Support.

Resolving query performance issues

After you've determined that a query is all or part of a performance issue, there are several approaches you can take to remedy it:

• Use caching to reduce the number of times the query is called
• Alter the query to make it complete more quickly
• Remove the portion of your website that is generating the query
• Monitor the server

Use caching

Caching is recommended across your website, on any website that gets more than a few visitors. Acquia Support frequently sees issues with websites that have uncached blocks, panels, or views. Views and Panels, particularly, do not turn on caching by default, and you must turn on caching for each specific view or panel individually.

The Caching overview can give you some initial pointers for your full website, especially if you're using blocks, panels, or views. There are also some more specific Views caching tips and tricks.

If the query comes from custom code, you may want to consider using Drupal's cache_set() and cache_get() functions to provide caching for it.

Alter the query

Depending on how the query is generated, it may be possible to alter it using a hook provided by Drupal or the contributed module that generated the query. There are several methods you can try when altering a query:

• The Views module does a best guess on what it thinks your query should be. It can use a table that is suboptimal, or gather more information than necessary. Slow queries are frequently caused by combining two or more large tables together using a JOIN. Review the number of joins in your query, and determine if the query is pulling more information than is actually needed. If you can simplify the query by using fewer joins or different types of join, this may help reduce the problem. See this link for documentation on the JOIN syntax.
• Some tables may not have indexes on them, which greatly slows down performance. Check your tables for indexes, and consider adding them in appropriate cases. Our operations team checks for errors resulting from missing indexes and if they detect this problem, a member of the Acquia Support team will reach out to you to advise you on the next steps.
• Queries that involve COUNT(*) to count all rows in a table can be slow if a table is very large. Acquia Support frequently sees COUNT(*) queries in the default pagers for Views. If your website uses pagers in Views and your slow query is for the pager, we strongly recommend the Views Lite Pager module, which uses queries that are far less resource-intensive.

If you're unable to determine if your query can be altered, open a ticket with Acquia Support to get more information about the source of the problem. Acquia Support can help you determine if a quick fix is possible, or put you in touch with your account manager to learn more about how Acquia’s Professional Services team can assist you with an in-depth optimization of your use of SQL queries.

Disable or remove the query

If it's not possible to alter a query or cache it, you may need to consider temporarily disabling it to preserve your website's uptime. Acquia Support may be able to suggest what portion of your website to disable or remove, while a Professional Services engagement may be required to perform an in-depth optimization.

Monitor the server

New Relic  is a monitoring service that can help you diagnose website problems. Acquia Cloud provides configurations for the New Relic application, an Application Performance Monitoring (APM) tool that you can use with your site. These services can also alert you when your website is experiencing problems. Find out how to enable New Relic services.