The Views module is a powerful features Drupal offers. With it you can build a rich & sophiscated display of data in minutes. But great flexibility comes with a price, Views makes alot of assumptions that can hamper your site’s performance. But there are ways to get around it.
Drupal stores the main record for each content type in the node table. So regardless of whether an entry is an article or a page, a record is made in this table. The advantage is, that building aggregated lists of all entries is easy.
What happens in Views?
When you build a view that uses a field specific to a content type - say, article - it has no clue if every entry will have this field or not. So while querying the db, it looks for the node and also say if there is a field called “category”, it looks for that as well.” This is called a LEFT JOIN, and it’s quite slow. On the other hand, if we change the query slightly to “give me this node and the corresponding field category,” then the whole view will execute much quicker. This is because, the query executes an INNER JOIN which joins each node entry with each category one to one.
But this will only work if you use one content type article. If you need to use multiple, say articles and pages, you are out of luck to some extend.
Pager in Views and Scaling
Biggest culprit in a view is the pager. Counting in databases is something that is slow. Getting rid of COUNT queries is an important optimization technique. To paginate accurately, Views executes COUNT queries on the entire result set. That’s a big problem becuase even if you’re displaying 10 records out of million of recored you might have, Views will count all of the millions of rows. You don’t have to be database expert to understand this.
Obvious solution is not always a solution. Yes, you can’t get rid of pagination entirely. But if in any case you can do it. So then we would have to use a less accurate pager.
Some ways to solve this:
Eliminates the “last” option. (You no more need to know total results)
Drop the ability to skip more than 2 pages ahead. (View can get rid of the COUNT query and use the current offset to build the pager.)
But wait, don’t worry too much. There is a module for that. Just use views_litepager.
Using Distinct in Views
When to use DISTINCT clauses in views? - never. Or just try to avoid them at all cost. Most cases, you can eliminate it by using a better query. Duplicates rows are usually sign of a bigger problem you have to solve any ways. Regardless of any reason or excuse you may have, it will slow down your query. What is more worse is - when you use DISTINCT, a GROUP BY clause is also inserted. Which inturn slows down the query further.
Disabling DISTINCT will solves both problems. Just make sure you are’nt getting duplicate results. Using INNER JOIN as mentioned above, its very unlikely.
Below is a quick implementation example.
The above code assumes that the view name is my_view and that the content type is article. It also pretend that we are having a module my_module.
Please note that it is also possible to enable/disable distinct option from views ui.
Also read this post. To see one real world example where left join was creating problem and how they fixed it.
Additionally you could consider enabling cache on your views. It can be found on the last column of views under the heading other.
Set it, depending on how often your data displayed in this view is update.
Other than this, you can disable SQL rewriting. But you have to do this with much care. Any hooks on the query or node_access check etc will not more work.
And if you have a really big data set, you could also enable to use a slave server.
Views are really powerful and handy and one of the reason many people use Drupal. With just a little bit of code and effort, we can make great improvements to views.