Optimizing Views queries - a mini case-study

I am sure anyone has ever created a Drupal View that is not ideal and should be made better. But in the same time, if you have a high-traffic site, you might find it hard to redo such a view due to any dependencies that the view might have. This mini showcase will provide some info on how we managed to fix the view without changing it - by altering it's query.

We have been approached with problems on one site that uses a very complex view with a query that usually takes more than 9 seconds on MySQL. The query was frightening:

<?php
SELECT  node
.nid AS nid,    node_comments__comments.subject AS node_comments__comments_subject,    node_comments__comments.comment AS node_comments__comments_comment,    node_comments__comments.format AS node_comments__comments_format,    node_comments__comments.cid AS node_comments__comments_cid,    node_comments__comments.nid AS node_comments__comments_nid,    node_comments__comments.name AS node_comments__comments_name,    node_comments__comments.uid AS node_comments__comments_uid,    node_comments__comments.homepage AS node_comments__comments_homepage,    node_comments__comments.timestamp AS node_comments__comments_timestamp,    flag_content_comments.timestamp AS flag_content_comments_timestamp,    'internal_comments:page_1' AS view_name  FROM node node LEFT JOIN comments comments ON node.nid = comments.nid  INNER JOIN node node_comments ON comments.nid = node_comments.nid  LEFT JOIN flag_content flag_content_comments ON comments.cid = flag_content_comments.content_id AND flag_content_comments.fid = 4 LEFT JOIN comments node_comments__comments ON node_comments.nid = node_comments__comments.nid   INNER JOIN node_access na ON na.nid = node.nid LEFT JOIN domain_access da ON node.nid = da.nid WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 2 AND na.realm = 'workflow_access') OR (na.gid = 10202 AND na.realm = 'workflow_access_owner'))) AND ((da.realm = "domain_id" AND da.gid = 4) OR (da.realm = "domain_site" AND da.gid = 0)) AND (  (node_comments.status <> 0 OR (node_comments.uid = 10202 AND 10202 <> 0) OR 0 = 1) AND (node.type not in ('broadcast', 'gallery', 'image', 'page', 'queue_item')) AND (node.nid = 45264) )ORDER BY node_comments__comments_timestamp DESC;
?>

A combination of not ideally designed view, node access and domain module made it suffer. What we strongly realized is that if you take a look at these parts of the query:

<?php
...LEFT JOIN comments node_comments__comments...
...
LEFT JOIN comments comments...
?>

You will see that the comments table was joined not once but twice! Just removing the second JOIN from the query in MySQL command line was enought to speed up the query over 10 times.

We created a simple custom module and implemented a following function:

<?php
function module_views_query_alter(&$view, &$query) {
  if (
$view->name == 'internal_comments' && $view->current_display == 'page1') {
// Remove all references to the second join.
   
unset($query->tables['node']['node_comments__comments']);
    unset(
$query->table_queue['node_comments__comments']);

// Alter ORDER BY to reflect the first comments table.
   
$query->orderby[0] = 'comments.timestamp DESC';

// Check all fields and if they are from the second join, move them.
   
foreach($query->fields as $key => $field) {
      if (
preg_match('/node_comments__comments/', $key)) {
       
$newkey = preg_replace('/node_comments__comments/', 'comments', $key);
       
$query->fields[$newkey] = $query->fields[$key];
       
$query->fields[$newkey]['table'] = 'comments';
        unset(
$query->fields[$key]);
      }
    }
  }
}
?>

Also check out that we didn't touch 'alias' section of the changed fields. All the fields will still have 'node_comments__comments_fieldname' aliases, meaning that there won't be any changes in output and styling required.

We achieved a speed up of 800 % using this query alter, resulting in much easier query with the same results. Of course this is not ideal, you should rather change the view itself and fix it but that requires more changes than this alter, you can get significant speed improvement.

Jakub Suchý

Jakub is owner and founder of Dynamite Heads. Jakub is a member of Drupal Security Team and supports Czech Drupal community at Drupal.cz