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.

Custom Contact Forms 5

March 20, 2012 by Taylor Lovett

Big news for Custom Contact Forms! After months of developing, we just released version 5. CCF version 5 is going to change the ways you build your forms dramatically. The theme for this new version is improved admin user interface.

New Features:

  • Rearrange attached fields with drag-and-drop
  • Rearrange attached field options with drag-and-drop
  • Restyled form and field manager
  • Save and delete buttons next to each form, field, field option, and style
  • Bugs fixed

Development will continue on Custom Contact Forms. Stay tuned.

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

5 Tips for using AJAX with JQuery and PHP in WordPress

November 12, 2010 by Taylor Lovett

Hey guys, I haven’t been posting as regularly as I like to be posting. Right now Custom Contact Forms 4.1.0 is in the works and I am doing a few freelance SEO/web development projects. While trying to debug the Custom Contact Forms admin JQuery/AJAX features I stumbled upon a great article.

If you write plugins for WordPress, this is a must-read. This article goes in to detail about a lot of the best practices for using Javascript with PHP in WordPress. 9/10 plugins are not using JQuery properly and thus cause havoc for other plugins (like mine!).

Check this out: 5 Tips for using AJAX with JQuery and PHP in WordPress

Custom Contact Forms 4.0.0

October 26, 2010 by Taylor Lovett

Custom Contact Forms 4.0.0, the most intuitive and customizable contact form plugin for WordPress, is about to be released. Here is a short list of some of the new features being added:

  • Import/Export/Create Backups at the click of a button
  • New style options for your forms
  • Ability to add a different CSS class to each field
  • * All form submissions saved to database and displayed in administration panel in a visually appealing way. You can enable/disable the emailing of form submissions now.
  • * AJAX in the admin panel – now you can save, delete, and manage everything in the admin panel without refreshing the page. This will save you time.

Right now you can download the latest 4.0.0 beta version on the WordPress CCF Download page. Right now the only thing that is left to do is debug the new AJAX features. We need debuggers. Right now we are adding anyone who helps us with debugging to the WordPress page as a contributor with a link to the website of their choice. Email admin@taylorlovett.com if you’re interested.

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!

Custom Contact Forms 1.0 Released

July 18, 2010 by Taylor Lovett

My new WordPress plugin, Custom Contact Forms 1.0, has finally be released.

Custom Contact Forms is a WordPress plugin for handling and displaying custom web forms; insert the provided code in any page, post, category, or archive in which you want a web form to show. This plugin allows you to create fields with a variety of options and to attach them to specific forms you create.

Definitely allows for more customization than any other WordPress contact form plugin out there today. The greatest part of this plugin is the comprehensive admin page which allows you to manage fields, forms, and tons of customizable options in one place! Also comes with a web form widget to drag-and-drop in to your sidebar. It has been tested on WordPress version 2.7.1 and up. Give it a try on your blog today; it’s free!

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()

The New 2010 Taylorlovett.com Homepage Is Up

June 12, 2010 by Taylor Lovett

Welcome to the 2010 version of Taylorlovett.com. This mission of this website is to bring web development solutions to small businesses. Based in Rockville Maryland, I specialize in websites and blogs (usually WordPress based), search engine optimization, and Google Adwords consultation. Please see my affordable web development solutions for more information on how I can improve the functionality and overall “feel” of your website as well as increase your websites internet exposure. If you would like to see some fine examples of my work, please see my web development portfolio. Welcome to the new site!

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.