Skip to main content

Drupal views optimization - Filtering by taxonomy term

I have a client site that has always been a bit sluggish. I wrote part of it off to it being fairly large and very complex with a ton of modules and such.

It grew and grew to hundreds of users and 5000+ nodes and we decided it was time to move to dedicated. The migration was completed nicely and the site was up. Since we switched to dedicated I was able to do some improvements, replacing the search module ( a huge resource hog) with apache solr and doing some server tweaks that made things better.

Time goes by and a couple months down the line I start looking at the site again. Everything's chugging along as usual but the nodes had doubled to 10,000+ and things were SLOW. I started doing some performance checks and saw that it was 12-14 seconds before the server side processing finished and data would start going across the wire.

There's a lot of content, images and images being transferred, but the php code shouldn't by any means take that long before it event BEGINS to serve the data. I looked at the resource utilization on the server, and MySql was using 140% of one core (that's how apache measures it, it translates to something like using all of one core, and some of another). I started digging into my views which I figured would be the likely culprit. Nothing major struck me as wrong. Views tends to do decent optimization and the queries looked fine. However, just to be safe I fired up phpmyadmin and ran the query along with an EXPLAIN. A portion of the query wasn't using an index! I looked at "preview" in the Views UI focusing on the query execution time and ran it several times. The query averaged 1.5 - 2 seconds! Each page on the site had 4-6 blocks doing similar queries! No wonder my load time was so high!

I started looking into the mess I had gotten myself into... I wasn't doing anything out of the ordinary. I was getting all nodes that had a taxonomy term of X. What I realized when I started thinking of this is that examining the taxonomy term by name joins the node.nid to term_node.nid, which joins to term_data.nid to find the name of the term. This is an extra join on all those ids and THEN looking for the name! Instead of using Term:Name in my view, I changed it to use Term:Id and placed the ID instead. This changed EVERYTHING. it went from 1.5-2 seconds down to 10-20 milliseconds.

Do NOT filter a view by Taxonomy Term: Name if you don't have to, and always use "Explain" before making a view live to make sure it's using the indexes.

Comments

Dedicated is always the best

It is always better to switch to a dedicated server if the load is too much. I have a bestessayservicereviews.com website that was developed with little programming. As the number of visitors and requests increased, I switched to a dedicated one. The process was smooth like you mentioned and now there are down times or errors anywhere.

Blackmart Alpha APK is an app

Blackmart Alpha APK is an app or rather I can say it’s the best online marketplace where you can get all the Android apps totally free of cost.Blackmart Apk Download

This is the article to whom I

This is the article to whom I was searching for a long time and now I found it here, I enjoy after finding this precious post and also would like to say thanks to you for such a informative post. You have done a very impressive work. http://patch.com/washington/woodinville/free-background-check-online-guide-how-to-search-all-public-and-criminal-arrest-records

kerala sslc results 2017

kerala sslc results 2017
Kerala State Board has conducted Secondary School Leaving Certificate (SSLC)

Good Information

Drupal is being popular day to day. The reports of SMO are showing that many developers and programmers are very impressed with the features of drupal web services. This article is very informative and do share such things.

Thanks for this article.

Thanks for this article. Great effort. Good work. Brilliant and highly efficient.
Buy Original Research Papers

Uncertainty stares at tens of

Uncertainty stares at tens of thousands of CBSE Class 12 students across the state as the results are expected only on May 27 or 28, after an unusually long gap of 20 days since the state board results were announced. http://cbseresults.nic.in/

I ever heard about this

I ever heard about this feature and I like working on drupal application. I have known many things about it in this website. I really got a good information about this new feature.

summary of Hamlet

Sachin Tendulkar is the best

Sachin Tendulkar is the best player in the history of world cricket.

Maharashtra Board Results

Results to the Maharashtra Common Entrance Test (MH-CET), an examination meant for engineering and pharmacy courses, were declared on Saturday, and students who appeared for the test were able to check their grades on the official website.

MahResult.nic.in

nice post

If you want to get latest New on Telugu Wap than check here .

good post

If you want to prevent your account from Facebook Phishing than check here .

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.
  • You can enable syntax highlighting of source code with the following tags: <asp>, <c>, <cpp>, <cs>, <css>, <drupal5>, <drupal6>, <html4strict>, <java>, <javascript>, <jquery>, <php>, <python>, <ruby>, <sql>, <xml>. The supported tag styles are: <foo>, [foo]. PHP source code can also be enclosed in <?php ... ?> or <% ... %>.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.