Horizontal Meta, developed by Nathan Franklin, is a WordPress plugin that attacks meta query performance issues head on. Horizontal Meta changes part of the WordPress meta data structure for users and posts into a horizontal layout which speeds up WordPress meta data querying exponentially.

Over the last 6 months, I have been bent on getting the maximum possible performance out of my WordPress installs. I have spent countless hours reading, testing, changing web hosts, coding and recoding, looking for the answers to my problems. One thing stuck out like a sore thumb was WordPress’ structure for storing meta data. 

If the purpose of meta data was purely for storage then there would be no issue, however, due to flexible nature of meta data plugin developers and theme developers alike have pushed WordPress to it’s limits, extending, revising and creating amazing products which integrate into the core well, however, sometime leaving performance to be less than desired.

This is where Horizontal Meta comes into the fold. A new plugin which has been developed to address some of these performance issues. Horizontal Meta works by allowing users to map certain meta data keys to the horizontal structure. Horizontal Meta will copy the existing meta data into the horizontal structure and will act transparently in the WordPress environment.

Performance Metrics

The test environment:

  • MacBook Pro 2.4GHz Core i5 with 8GB ram. 
  • approx 70,000 post meta records.
  • approx 1,100 posts, pages, custom posts.

WordPress query:

$args = array(
  "post_type" => "project",
  "numberposts" => -1,
  "posts_per_page" => -1,
  "meta_query" => array(
    array(
      "key" => "_dr_projects_funded",
      "value" => 0,
      "type" => "NUMERIC"
    ),
    array(
      "key" => "_dr_projects_total_project_cost",
      "value" => array(1000, 20000),
      "compare" => "BETWEEN",
      "type" => "NUMERIC"
    )
  ),
  "meta_key" => "_dr_projects_project_progress",
  "orderby" => "meta_value"
);

This query is requesting all the posts of type ‘project’ that have a meta data field ‘_dr_projects_funded’ which equals ‘0’ and another meta data field ‘_dr_projects_total_project_cost’ which is between 1,000 and 20,000. We are then ordering by the value of the meta field ‘_dr_projects_project_progress’.

The following SQL string will be generated by the WordPress core:

SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_postmeta
  ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1
  ON (wp_posts.ID = mt1.post_id)
INNER JOIN wp_postmeta AS mt2
  ON (wp_posts.ID = mt2.post_id)
WHERE 1=1
AND wp_posts.post_type = 'project'
AND (wp_posts.post_status = 'publish')
AND (wp_postmeta.meta_key = '_dr_projects_project_progress'
AND (mt1.meta_key = '_dr_projects_funded'
AND CAST(mt1.meta_value AS SIGNED) = '0')
AND (mt2.meta_key = '_dr_projects_total_project_cost'
AND CAST(mt2.meta_value AS SIGNED) BETWEEN '1000' AND '20000')
)
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value DESC

-- 250 rows in set (0.31 sec)

After Horizontal Meta is applied, the above WordPress query will generate the following SQL string:

SELECT wp_posts.* FROM wp_posts
INNER JOIN wp_postmeta_hm as hm_meta
 ON hm_meta.obj_id = wp_posts.ID
WHERE 1=1
AND wp_posts.post_type = 'project'
AND (wp_posts.post_status = 'publish')
AND ( hm_meta.int1 = '0'
 AND hm_meta.decimal2 BETWEEN '1000' AND '20000' )
GROUP BY wp_posts.ID
ORDER BY hm_meta.int2 DESC

-- 250 rows in set (0.02 sec)

By adding Horizontal Meta, the page speed has increased the page loading speed by 290ms, and that is only for a single query. This one query executes 17x faster using Horizontal Meta

In addition to these stats, further optimisations can be applied by indexing individual columns in the Horizontal Meta table, however this is not covered here.

Does it really save that much time?

A saving of 290ms might not seem like that much, but it is important to remember the benchmark is for a single query. In a production environment a code heavy WordPress page may require multiple queries similar in nature to this which can begin to impact performance significantly without optimisation. Even in the case of only 1 additional query like the example above, you could potential be saving over half a second on your page load. If your site is large enough and your pages are accessed frequently enough, it will important for them to be as optimised as possible.

Plugin Details

You can download the free version of Horizontal Meta from the WordPress plugin repository.