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.