SEO and WP-eCommerce Performance Tuning

We have been doing some tuning work on the 80,000+ product Sparkle Gear WP e-Commerce store and wanted to share one experience with the WordPress SEO Plugin by Yoast.

Unless you are a SEO expert, Yoast’s WordPress Plugin is a a tool that will save you alot of work, and keep much the SEO process understandable and accessible to mere mortals.  It’s a great addition for any WordPress site, and especially useful for WPEC that create lots of posts (products are posts) and have lost of taxonomies (variations and categories are taxonomies). IMHO. the dynamic sitemap generation feature of the WorpPress SEO plugin alone makes it a must have.

However, there are some details about how WPEC is implemented that need to be addressed when you install the SEO plugin.First item, WPEC stores products as posts. WPEC stores 

variations of products as child posts of product posts.  These child posts are used to hold information about an individual product variation.  Details like the variation image, price, stock. etc.  The important detail to note is that these child posts are not directly accessible to the outside world. In WP speak, the permalink for these posts will give you a 404!

The problem comes about when the WP SEO plugin tries to generate the sitemap for your products.  The default behavior is the plugin will retrieve from the database the list of all posts, including the child posts. The permalinks to the child posts are then included in the sitemap.  When Google  Bing, Yahoo, or any search engine, used the generated sitemap to retrieve these links from the sitemap they get your 404 Not Found page.This means a lot of unnecessary traffic to your web site, lots of errors in your logs, and perhaps a lowering of your search ranking.

There isn’t an easy way to stop the SEO plugin from using the child posts unless you want to change the plugin source code.  You also don’t want to change this behavior  child posts should be included for all post types except for WPEC product posts.  Fortunately, Yoast did a great job providing some easy to use ‘hooks’ to alter the plugin behavior.

Add this code to your WordPress functions.php and the plugin will only include products that don’t have a parent.

function no_wpec_product_children_where($ignore, $type) { $filter = ”; if ( $type==’wpsc-product’ ) { $filter = ‘ AND post_parent = 0 ‘; } return $filter; } add_filter(‘wpseo_posts_where’, ‘no_wpec_product_children_where’,10,2); function no_wpec_product_children_where_count($filter, $type) { if ( $type==’wpsc-product’ ) { $filter .= ‘ AND post_parent = 0 ‘; } return $filter; } add_filter(‘wpseo_typecount_where’,’no_wpec_product_children_where_count’,10,);

 

The second issue that you will have (at least in Version 1.3.4.4) is the database query that is used to create the main (root) sitemap for your site.  When the plugin creates the sitemaps for the product post type it will use the filter you created above.  The main sitemap is not using the filter when it does it’s magic.

The change is very simple to make.  Find the wordpress-seo directory. There is a subdirectory ‘inc.  In this directory you want to edit the file ‘class-sitemaps.php’. Find the function ‘build_root_map’. Change the lines from:

$query = $wpdb->prepare( “SELECT COUNT(ID) FROM $wpdb->posts WHERE post_type = ‘%s’ AND post_status IN (‘publish’,’inherit’)”, $post_type ); $count = $wpdb->get_var( $query );

to:

$where_filter = ”; $where_filter = apply_filters( ‘wpseo_typecount_where’, $where_filter, $post_type ); $query = $wpdb->prepare( “SELECT COUNT(ID) FROM $wpdb->posts {$join_filter} WHERE post_status IN (‘publish’,’inherit’) AND post_password = ” AND post_type = %s ” . $where_filter, $post_type );

Now your sitemaps will work properly.

One little note… While you are editing this file you might also want to find the SQL queries that look for posts with an empty password.  Because WordPress does not index the password field these queries cause the database server to do full table scans.  In a WPEC site with a lot of products the performance degradation can be very severe.  Unless you have password protected posts in your WPEC store (unlikely?) you can remove the AND password=” from the queries and keep your site running smoothly.

We already suggested these changes in the plugin’s support forum at wordpress.org.  The plugin is very well supported and we expect that the change will eventually find it’s way into the code base. Until then this little bit of extra work is definitely worth while for your WPEC store.