We recently stumbled on a performance issue on a big project. Loading time was really high on some pages, and we soon came identify a couple of queries slowing down the site. I'd like to show this as a good advice. You shouldn't trust Views + CCK completely when it comes to performance. The popular suite of modules is aimed on softening the process of developing website architecture, so you don't have to write down your queries all the time (and potentially rewrite 2 or 3 times for every change in the project). As much as it is time-saving, it also needs some attention and analysis when you're dealing with complex queries on huge datasets.

Our little case can demonstrate this. We have a node table with ~50K nodes and a user table with ~10K. We used Content Profile for a flexible customization of user profiles, and at some point we'd like to pull a page of nodes with author information for each of them. In short terms, the view produced:

SELECT many fields
FROM node node
INNER JOIN users users ON node.uid = users.uid
LEFT JOIN node node_users ON users.uid = node_users.uid AND node_users.type = "content profile name"
  a couple of other JOINs
WHERE (node.type in ('node type')) AND (node.status <> 0)
ORDER BY node_created DESC

I've simplified it to the important part. Even if you remove the other joins and fields, you will still get a VERY SLOW query. If you check how many rows were examined, you'll find astronomic values > 1M, all of that just to select a dozen nodes. The trouble are those 2 joins, and particularly the last one. Too many joins cause the table to be really large, cosuming a lot of memory and taking a lot of time to filter. I don't know much about MySQL inner working, but experience shows that.

How to deal with it then? We don't want to loose flexibility and speed while developing, but we can't afford to take 12s (in our case, we had such query) to load a page, right?

Then, first of all, we need to make the analysis. Turn on slow queries log on MySQL, and find the view causing the problem. After that, try removing JOINs one by one to see which one is really making it get slow. Usually Content Profile can cause that, because it involves joining the node table again just to filter out very few nodes out of it.

After identifiying the trouble, you can use the solution I used (it boosted the speed up to 10x in our case): hook_views_query_alter()

/**
* Implementation of hook_views_query_alter()
*/
function epd_tweaks_views_query_alter(&$view, &$query) {

  foreach ($query->table_queue as $table => &$table_info) {
    if ($table_info['table'] == 'node' && !empty($table_info['join']->extra)) {
      $extra = $table_info['join']->extra;
      unset($table_info['join']->extra);
      $filters = array();
      foreach ($extra as $filter) {
        if (!is_numeric($filter['value'])) {
          $filter['value'] = '"' . $filter['value'] . '"';
        }
        $filters[] = ' ' . $filter['field'] . ' = ' . $filter['value'];
      }

      $table_info['join']->table = '(SELECT * FROM {node} WHERE ' . implode(' AND ', $filters) . ')';
    }
  }
}

This code checks the query for left joins with extra conditions, like the one causing the trouble before, and substitutes the 'node' table with a subquery with the extra condition applied (and removed from the Join, obviously). With this, the subquery is run first, and the JOIN occurs only with a small subset of it. Additionaly, a bonus is granted to us, as the subquery is cached because it is very simple and has a small result!

Finally, the speed is back. Be sure to check the results to see if they're consistent. Using the view preview is a nice place to check if the query is being properly altered and if the query time is really reduced.