Using Views SQL query signature to find slow queries

Slow queries

If you're using the Views module, you may wonder if a Views-generated query is slowing down your site. To add a debugging signature to the SQL (structured query language) queries that it generates, enable the Add Views signature to all SQL queries check box.

Enabling Views signatures

If you have the Views UI module enabled you can find the Add Views signature to all SQL queries check box on the Views configuration page, based on your installed version of Drupal:

  • Drupal 7 and 8 - Go to Structure > Views > Settings > Advanced (http://[site_URL]/admin/structure/views/settings/advanced)
  • Drupal 6 - Go to Site building > Views > Tools (http://[site_URL]/admin/build/views/tools)

To add a debugging signature to the Views-generated SQL queries on production sites where the Views UI is often disabled, use Drush. At the command line, enter the following:

drush vset views_sql_signature 1

Using signatures to evaluate queries

With Views signatures enabled, any Views-generated query will include the name of the view and the display name in the format of view-name:display-name as a string at the end of the SELECT statement.

As an example, the following SQL query is the default Archive view that comes with the Views module, using the default display. Drupal displays the query for site admins next to a Query label at the bottom of the page. You can see 'archive:default' as AS view_name, which is the query signature.

SELECT DATE_FORMAT((DATE_ADD('19700101', INTERVAL node.created SECOND) + INTERVAL -25200 SECOND), '%Y%m') AS created_year_month, COUNT(node.nid) AS num_records, 'archive:default' AS view_nameFROM{node} nodeWHERE (( (node.status = '1') ))GROUP BY created_year_month, view_nameORDER BY created_year_month DESCLIMIT 30 OFFSET 0

Using the Devel module, Views' own query stats, or (if you're on Acquia Cloud) your slow query log, you can attempt to find the query that's causing processing-time issues. If the query is from Views, consider setting up caching for that query.

Cleaning up

When you're finished evaluating your Views queries, you can disable the Add Views signature to all SQL queries box in the UI just as you enabled it, or use Drush to set the variable to 0. Note that it's not necessary to disable this option, and having it continue to be enabled shouldn't cause any performance impacts.

Contact supportStill need assistance? Contact Acquia Support