Find slow Drupal views from a slow query log

Introduction

Often slow queries are generated by some Drupal View in a website. But from looking a slow MySQL query itself, you have no indication of which View the query came from, or even the query even came from a Drupal View at all. This tip goes a long way towards solving that problem, by highlighting a configuration option in Drupal that adds signature to each query generated by a View that includes the name of the View that generated it. In the opinion of many this option should be enabled by default, since should have little impact on performance.

These instructions are for Drupal 8, but the same option exists in Drupal 7.

Steps

1. Enable views signatures in the Views UI advanced settings.
   (In D8, that's in /admin/structure/views/settings/advanced under the "Debugging" fieldset and the option is "Add Views signature to all SQL queries")
2. Wait for slow queries to happen over the course of an hour or a day or a week.
3. Find the slow query log file (not the digest, the raw log).    If it is an Acquia Cloud site the slow query log can be downloaded from Logs page.  If it is a Dev Desktop site,  the local file path is shown in the Logs tab of the Dev Desktop settings in the "MySQL slow queries" field.
4. Run the following bash snippet as shown in the example, to list the most common 10 views appearing as slow queries

5. cat slow.log  | egrep -o "\S+'\ \AS\ view_name" | awk '{print $1}' | sort | uniq -c | sort -nr | head

   Note: You will have to edit the filename of the slow query log, to whatever yours is called. For example, it may be something like slow_query_log_[sitename]_[timestamp:1349352001].sql

(If you cannot run the command line, you can search for the string "AS view_name" in a the text editor of your choice.)

In general, when troubleshooting slow queries, you should also turn on the Views setting to show the query above a Views preview when editing each view in the Views UI module:

With this setting, you can see the SQL query that is made with the view when editing the View with the Views UI module, then you will fully understand what to search for in the slow query logs, and this entire method will become clear.