TaylorLovett

Why Are Taxonomy Lookups Faster than Post Meta Lookups?

May 14, 2013 by Taylor Lovett

So I’ve read all over the place that looking for posts based on a taxonomy is much quicker than looking up posts based on post meta. But why? I’ve yet to read any sort of technical explanation that satisfies my curiosity. I decided to do some research.

I wrote some example code and used the debug plugin to examine the resulting SQL queries. Here is a post meta query:

$args = array(
  'meta_query' =>
    array(
      array(
        'key' => 'test_key',
        'value' => '1'
      )
    )
);
$query = new WP_Query( $args2 );

Here is the resulting SQL:

SELECT SQL_CALC_FOUND_ROWS trunk_posts.ID
FROM trunk_posts INNER JOIN trunk_postmeta ON (trunk_posts.ID = trunk_postmeta.post_id)
WHERE 1=1 AND
trunk_posts.post_type = 'post' AND
(trunk_posts.post_status = 'publish' OR trunk_posts.post_status = 'private') AND
( (trunk_postmeta.meta_key = 'test_key' AND
CAST(trunk_postmeta.meta_value AS CHAR) = '1') )
GROUP BY trunk_posts.ID
ORDER BY trunk_posts.post_date DESC
LIMIT 0, 10

As you can see the posts table is being joined with the post meta table. A join very basically creates a temporary table where every row in the posts table is matched with every row in the post meta table. The ON clause then narrows down that temporary table keeping only rows of posts that have been matched with post meta that apply to that post. Here is a database model of the post and post meta tables:

postmeta_table

This joining of posts with corresponding post meta rows is done by comparing values across two indexed columns (trunk_posts.ID and trunk_postmeta.post_id) as shown in the diagram. Creating an index on a column in MySQL makes lookups on the column much quicker. Simply put, MySQL stores a B-Tree of the values in that column. A B-Tree is a data structure that allows search in O(log n) time (vs. O(n) time on an unindexed column). The disadvantage in indexing a column with a B-Tree type data structure is that inserting and deleting becomes much slower because parts of the tree must be rebuilt; this is generally a worthwhile sacrifice.

Next, the WHERE statement narrows down posts by meta key only keeping rows that have meta_key=’test_key’. Again, as shown in the diagram, trunk_postmeta.meta_key is an indexed column. So again we can do this in O(log n) time. Awesome, so where is the slow down? Well, the last thing we have to do is narrow the posts so we only have ones where meta_value=’1′. There is no index on the meta_value column. We have refined our posts as follows:

  1. posts matching with every post meta row
  2. posts matching with only post meta that applies to that post
  3. posts matching with post meta for that post with key ‘test_key’
  4. posts matching with post meta for that post with key ‘test_key’ and value ’1′.

On the final refinement, if there are multiple post meta rows with the same key associated, then we will have to search through multiple values until we find a matching value. This search will happen in O(n) which is slow.

Now let’s look at how a taxonomy query works. Here is my taxonomy query code:

$args = array(
  'tax_query' => array(
    array(
      'taxonomy' => 'category',
      'field' => 'id',
      'terms' => array( 2 ),
    )
  )
);
$query = new WP_Query( $args );

Here is the resulting SQL:

SELECT SQL_CALC_FOUND_ROWS trunk_posts.ID
FROM trunk_posts INNER JOIN trunk_term_relationships ON (trunk_posts.ID = trunk_term_relationships.object_id)
WHERE 1=1 AND
( trunk_term_relationships.term_taxonomy_id IN (2) ) AND
trunk_posts.post_type = 'post' AND
(trunk_posts.post_status = 'publish' OR trunk_posts.post_status = 'private')
GROUP BY trunk_posts.ID
ORDER BY trunk_posts.post_date DESC LIMIT 0, 10

As you can see the trunk_posts table is joined with the trunk_term_relationships table. The two tables are joined into a temporary table which is refined by the ON portion of the join statement matching each term row with it’s corresponding posts.

taxonomy_table

As shown in the diagram there is an index on trunk_posts.ID and trunk_term_relationships.object_id, so this first refinement can be done in O(log n) time. Next the WHERE clause refines the temporary table so that only posts matched with the term id 2 are left. There is an index on the term_taxonomy_id column so this can be done in O(log n) time. Obviously, there is more to the query then just this but we don’t care about anything else for now.

Summary:

Post meta queries will experience a major slowdown if the key(s) being searched are associated with a large amount of posts. Another big reason to avoid post meta queries is because the post meta table is typically much larger than the taxonomy_term_relationships table. Therefore doing a join with posts and postmeta is a much more expensive operation than joining posts and taxonomy_term_relationships. This analysis doesn’t take into account caching and is drastically simplified. If you have any questions or corrections, please leave a comment.

10,000 Downloads!

November 14, 2010 by Taylor Lovett

Custom Contact Forms has been download over 10,000 times in it’s first 5 months. Thanks to everyone who has provided feedback and bug reports. 4.1.0 is in the works and hopefully will make Custom Contact Forms available to more types of server configurations. CCF has grown in to quite the plugin and is only gaining more features. In the future we hope to add lead capturing and internet marketing functionality. Thanks!

Download Custom Contact Forms

Custom Contact Forms 3.0.0 and 3.1.0

August 31, 2010 by Taylor Lovett

Custom Contact Forms 3.0.0 was released and shortly after so was 3.1.0. CCF has grown to be quite the plugin, packed with awesome features, many of which you will not find in any contact form plugin.

An interesting new feature is Custom Html Forms. This feature allows people who are skill with Html and Css to use CCF simply to process forms. Now you can write the Html for your forms using the code provided by this feature as a framework. This feature makes this plugin ideal for web developers.

CCF 3.1.0 also finally has a reusable widget. Create forms in the CCF admin page, then on the widgets page chose which form you want to display. As with all forms you can use the default style or create a custom style and attach it accordingly. The possibilities with this plugin are endless and, best of all, it is very easy to use and navigate.

A few other new features to mention:
Ability to not use Jquery – great if you want to keep your page load time down
Ability to use a custom thank you message and thank you message title individually for each form.
Ability to change the background color of text areas
Ability to include forms in your theme files via php code
Much more!

CCF 3.5.0 will boast the ability to add drop down and radio fields. A new manager will be added to the plugin, the Field Options Manager which allows you to create options for drop downs and radio fields.

Get excited!

Comment Template Customization in WordPress

June 24, 2010 by Taylor Lovett

Today I was working on my blog and started styling comments.php in my WordPress theme – this wasn’t too difficult but still took me a couple hours to sift through PHP/Wordpress jargon to find the HTML/CSS code I was looking for. Before long I wanted to style the actual comments posted by users. To my dismay I learned the code for user comments is not in the comments.php theme file but rather in the comments-template.php file in the WordPress wp-includes/ folder. Bummer.

Customizing the WordPress wp-includes/comments-template.php file is extremely complicated, even to a veteran PHP programmer, and after messing with it for 2 hours I started searching Google for a guide. I found out through various guides that the wp_list_comments() function called in comments.php can take a callback function as a parameter that will use a custom comment function as opposed to the WordPress default. By using the callback parameter to point to your own function, you can really take control of the WordPress comments style and structure.

I found a great guide that will take you step by step through creating your own comment function for the wp_list_comments() function to callback.

WordPress Comment Templates Customization

Also, for background information on wp_list_comments() and a full list of it’s parameters:

Function Reference for wp_list_comments()

New Site Features and Web Development Projects

December 30, 2008 by Taylor Lovett

So I have added a few little features to the site. I added a photo viewer to the portfolio section to view screen shots more efficiently. I also updated the about section. I plan to add a more comprehensive resume section when I have the time. These days I am working full-time for a construction company in Rockville, Maryland as Head of IT. I have mainly been making their websites more dynamic, as in content controllable through PHP/MySQL administration panels. More updates should follow this one shortly.

Portfolio Is Up and Running

June 1, 2008 by Taylor Lovett

The web development Maryland portfolio is now available. The portfolio contains projects ranging from web design and imagery to content management system integration and PHP programming. The portfolio contains three sections: Development, Imagery, and Management, Purely Scripts and Programming, and Freelance Images. Click on a project to learn more and to view screen shots.