Optimizing Drupal Views the Right Way

Posted on July 23, 2012

The Views module is one of the most impressive features Drupal offers. With some clicks here and there, you can build a rich display of data in minutes. But to offer this kind of flexibility, Views makes some assumptions that can hamper your site's performance. In this post, you'll find out how to improve performance dramatically.

First, a quick technical explanation of what happens. Drupal shares the main record for each content type in the node table. That means that regardless of whether an entry is a blog post or an event, it will get a record in this table. This has the advantage that building aggregated lists of all entries is quite easy.

On Joins

How does this tie into Views? When you build a view that uses a field specific to a content type - for example, event date - it doesn't know if every entry will have this field or not. So when querying the database, it tells it "give me this node. And if it has a corresponding field called event date, give me that as well." This is called a LEFT JOIN, and it's quite slow. If on the other hand, we changed the query ever so slightly to "give me this node and the corresponding field event date," then the whole thing will execute much quicker. By doing this, the query is executing an INNER JOIN which joins each node entry with each event entry one to one.

Learning Drupal? Subscribe to my Drupal articles, tips and tutorials.

Keep in mind, this will only work if you want the result set to contain events only. If you need to mix events and blog posts, you're out of luck. Well, sort of1.

On Pagers

Guess what the most expensive part of your view is? That's right, the pager at the bottom. Turns out, counting in databases is slow. Really slow2. So get rid of as many COUNT queries as you can. What's this got to do with pagination? To accurately paginate, Views executes a COUNT query on the entire result set. That means that even if you're displaying 20 records out of a million, Views will need to count all 20 million rows. This takes a long time.

So what are your options? You've got two3. One is to get rid of pagination entirely. The other is to use a less accurate pager. We won't go over the former, because clients/product managers can be notoriously inflexible - good luck trying to convince them to use no pagination. But a less accurate pager? They can probably live with that.

So how does a less accurate pager work? First, it eliminates the "last" option. That means you don't need to know how many total results there are. Skipping more than 2 pages ahead at a time is also nixed. With these compromises, you can get rid of the COUNT query and simply use the current offset to build the pager. You'll only be able to accurately jump to pages before the one you're on, but often this is good enough.

On Distinct

DISTINCT clauses have their time and place - never. No but really, try to avoid them. Most of the time, you can eliminate the need for them by crafting a better query. Duplicates rows are usually a symptom of a bigger problem. So you shouldn't mark your view queries as needing DISTINCT unless you're absolutely certain it's needed. Or if you're lazy. Regardless of the reason, it will slow down your query. Worse - when you use DISTINCT in a view, a GROUP BY clause will be inserted as well. This will also slow down your query significantly. Fortunately, disabling DISTINCT will rid you of both problems. Of course, do check to make sure you're not getting duplicate results. If you're using INNER JOIN as described above, you most likely won't.

The Changes

After all that technical talk, let's actually apply these changes. Three assumptions:

  1. Let's assume your view's machine name is my_view.
  2. Let's also assume it's joining to a table called content_type_event for the additional event information.
  3. Let's pretend you've got a module floating around called my_module.

Here's how to do this The Right Way™:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
<?php                                                                          
/**                                                                             
 * Implements hook_views_query_alter().                                         
 *                                                                              
 * Disables distinct and turns LEFT joins into INNER joins. This drammatically  
 * speeds up performance.                                                       
 */                                                                             
function my_module_views_query_alter(&$view, &$query) {                         
  if ($view->name == "my_view") {                                               
    $query->table_queue['content_type_notice']['join']->type = 'INNER';         
    $query->distinct = 0;                                                       
  }                                                                             
}

What about the pager? That's even easier. Just download the lite pager module and select it as the paging option for your view.

Conclusion

Views can be really helpful, no doubt. And it can get a bad rap about performance. But that's because - without much user input - it has to make a lot of assumptions to construct the query. With just a little bit of code, we can guide the query builder down the right path.


  1. You could just load the common data from the node table, then individually fetch the rest of the information with a query per row. 

  2. If you're using the MyISAM table engine for MySQL, disregard this. 

  3. There's a third option: query hinting. But it's beyond the scope of this article. 

Comments

Adedapo Harjuone AjuwonNov. 2, 2017, 10:42 a.m.

Highly resourceful content you have here. Subscribed already

Reply
jessica alexFeb. 22, 2019, 10:37 a.m.

Wow! I’m browsing away perusing your web journal from my lap! Simply needed to say I adore perusing your website and anticipate every one of i need an assignment your posts! Carry on the eminent work.Feel free to browse for getting more information about writing work

Reply
hijMarch 7, 2019, 10:11 a.m.

Nice blog, the article you have shared is good.This article is very useful. My friend suggest me to use this blog. MBA college in punjab

Reply
hijMarch 28, 2019, 11:17 a.m.

Apple products are revered for its quality, precision and great design. SRSG started its operations as Apple technology partners in the year 1997.
Apple reseller in Mumbai
Apple authorized service center in Kolkata
ipad service center in delhi
Radio station consultants
system integration service providers

Reply
Kaitlyn JohnMarch 29, 2019, 11:39 a.m.

Thank you for sharing such an informative article with us. Many concepts on Optimizing Drupal Views the Right Way is shared here. dermal fillers Many informative things such as on joints, on pages, etc, are clearly explained. Even a beginner can understand what the author tries to share.

Reply
dsdsdMay 6, 2019, 5:35 a.m.

Il est également possible de voir des histoires sur les dommages causés aux intérêts des clients à partir des informations, replique montre suisse d'Internet et d'autres canaux. En conséquence, les clients ont tendance à être préparés pour les vendeurs, en particulier ceux qui sont actifs dans la vente sur site, et sont encore plus réticents à entrer en contact avec eux. La tag Heuer montre est associée à un bracelet en caoutchouc doublé de tissu qui améliore la durabilité du bracelet et ajoute au confort.

Reply
NikieMay 10, 2019, 12:06 p.m.

We offer calculated visibility for the design and development of technology platforms through ourintensive research-based approach, consistency in performance, and adept decision-making caliber.
website design company in malaysia
online digital marketing malaysia
Search engine optimization companies in malaysia

Reply
Alan MartinJune 9, 2019, 12:25 p.m.

Can I simply just say what a comfort to find somebody that really knows what they're discussing on the internet. assignment writing help uk You definitely understand how to bring an issue to light and make it important. More and more people really need to read this and understand this side of your story. It's surprising you're not more popular since you certainly possess the gift

Reply
Jacob liamJune 10, 2019, 1:16 p.m.

Assignment Help Online Online provides you best support on the assignment related to management and business and business operations.Accounting Assignment Help

Reply
Golalai IsmaeelJune 24, 2019, 11:52 a.m.

The amazing way to discribe the method to optimize drupal views. I appreciate your thoughtful writing. I've tried the same thing on top android spy software in the market and found it very optimized at the end.

Reply
John NoelsJuly 6, 2019, 6:40 a.m.

Instant Assignment Help offers the best online assignment writing help to the college students who wish to score top grades in their academic career.

Reply
GosJuly 12, 2019, 10:36 a.m.

The goal of the society is to create professionally well skilled students To achieve the said goal, arrangements have been made with the pioneers and front runners both in India and abroad.
top cbse school in premnagar
The Health system is created in a proficient way of expanding the network in the multispecialty services. With the ever-growing population, the hospital has announced many ambitious plans that have ultimately lead to the excellence.
multispeciality hospital in hisar
ivf treatment in hisar

Reply
GGJuly 27, 2019, 12:11 p.m.

Good Post

Reply
ninaAug. 2, 2019, 6:48 a.m.

yeah i am also facing this when i was visiting chiltan pure moringa oil product page by the way thank you for letting us now good work dude wanna know visit here.

Reply
kimyAug. 2, 2019, 6:51 a.m.

it's just coincidence that i have see this on different website so i just wanna say my friend buying kishmish organic cucumber face mask for further info you can visit it's a best face mask ever goood work.

Reply
alyAug. 2, 2019, 6:56 a.m.

thank you very much for such a great information that's a very good conclusion. because last day i was searching about gloss whitening cream so i got a on a website that's have a problem by the way wanna know more about whitening cream visit there.

Reply
hrtAug. 15, 2019, 9:58 a.m.

生完孩子怎么丰胸好一:多锻炼。因为锻炼是丰胸的良方丰胸产品,参加体育锻炼,尤其注意锻炼胸部肌肉,使胸大肌发达,促进乳房丰满产后胸部下垂怎么丰胸。平时注意站立和行走姿势,经常保持挺胸收腹女性丰胸最快方法,以利乳房发育,参加游泳运动特别有助于双乳健美锻炼酒酿蛋丰胸效果

Reply
Terence MunozAug. 18, 2019, 9:43 a.m.

How to choose the tire size for your car? While tire size can be a bit intimidating at first for car buyers, if the car looks good on your spec sheet, you can start picking tires from this list that you can actually get good on. tiresout.com

Reply
Arpit SharmaAug. 31, 2019, 7:23 a.m.

This article is an appealing wealth of useful informative that is interesting and well-written. Thank you for always going above and beyond information to publish here. Travel ERP Software

Reply
FreyaSept. 5, 2019, 8:08 a.m.

This post is very helpful to me because of Myself a tutor in the IT industry. This post can be shared with my students for their reference. hoping for more posts in future.

Reply
donSept. 6, 2019, 11:52 a.m.

When you provide users with more infrastructure management solutions helpful content then it becomes a good idea to have a helpful site which really provides a big collection of thoughts through the website for readers. The wide variety of such appropriate thoughts and subjects makes the website more impressive to users of all kinds.

Reply
selfieSept. 18, 2019, 11:25 a.m.

Welcome to the world of gaming, where life is fast paced and interesting. There is always an adventure waiting for you online.
csgo smurf accounts
csgo vip accounts

Reply
kingSept. 20, 2019, 10:10 a.m.

Nice blog, the article you have shared is good.This article is very useful. My friend suggest me to use this blog.oxycodone where to buy

Reply
kyron rockoSept. 21, 2019, 6:18 p.m.

If you've been doing research on DHEA, you've probably also heard of 7-Keto DHEA. But what is the difference and which one is the right one for you? must read

Reply

Post New Comment