As part of my most recent project (which you’ll be hearing more about very soon), I’ve been working with WP e-Commerce and having a tonne of fun dealing with all the bits and pieces. In general, it has been quite handy, since it has meant I don’t have to deal with implementing all the payment handling and such. However, it does have its issues, including a fairly horrible API.
WPEC is also quite a bit inefficient, due in part to its customisability. However, it’s definitely nothing insurmountable with a bit of code and some clever tricks.
Note: I’ll be using code from 4.0-dev in examples, but it should all be the same for the latest stable version as well.
So, with all of that out of the way, let’s get started. First step in optimising anything in WordPress is to turn WP_DEBUG on. We’ll also want to turn SAVEQUERIES on so that we can see what exactly is getting queried. The Debug Bar plugin will also help to view the results of these.
To start off with, here’s the MySQL queries that were generated by WPEC for me on a non-WPEC page:
SELECT option_value FROM wpstore_options WHERE option_name = '_transient_timeout_wpsc_theme_path' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = '_transient_wpsc_theme_path' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = 'wpsc_replace_page_title' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = 'wpsc_hide_featured_products' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = 'base_zipcode' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = 'wpsc_ups_settings' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[productspage]%' AND `post_type` = 'page' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[shoppingcart]%' AND `post_type` = 'page' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[transactionresults]%' AND `post_type` = 'page' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[userlog]%' AND `post_type` = 'page' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = '_transient_timeout_wpsc_url_wpsc-default.css' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = '_transient_wpsc_url_wpsc-default.css' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = 'google_server_type' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = 'google_cur' LIMIT 1
That’s 14 queries for essentially nothing! Even worse are the four fulltext queries to find those shortcodes. Surely we can do better.
So, let’s start cutting pieces out. The first part that concerned me was the two google_ queries, as I’m not using Checkout. As it turns out, the Google Checkout plugin does all sorts of stuff even if it’s not loaded. This is not something we want. However, this is easy to fix. WPEC loads everything in the wpsc-merchants/ directory, but no other code relies on these merchants, so simply remove the ones you don’t need. We’re using Brent Shepherd’s PayPal Digital Goods payment gateway (which hopefully will make it into WPEC 4.0). This gateway uses the new 4.0 merchant gateway classes, so we don’t actually need anything in wpsc-merchants/. Before you remove all the files though, note that a blank directory will cause errors, so leave testmode.merchant.php to avoid this.
Right, we’re now down to 12 queries. Next job, cutting out the shipping information. Both base_zipcode and wpsc_ups_settings are being loaded, despite no shipping handlers being activated. As our store is purely virtual goods, we don’t need any of the shipping items, so we’ll do as before and remove them all. Be wary of the blank directory issue though, and leave at least one file in there (I chose flatrate.php).
OK, 10 queries! We’re making great progress. Next step is wpsc_replace_page_title and wpsc_hide_featured_products. Go into the presentation tab of your settings and resave, and this should save these to the database and set the autoload property, causing them to be loaded in the initial WordPress settings query. However, I noticed this was not happening on our server (I suspect that if they are set to off, they simply aren’t being saved), so I hardcoded them in the theme:
// pre_option_$x doesn't like false, so return 0 instead
add_filter('pre_option_wpsc_replace_page_title', '__return_zero');
add_filter('pre_option_wpsc_hide_featured_products', '__return_zero');
Of course, if you want to enable them, you should use '__return_true' here instead, however the settings page should work for this.
By now, we should be down to the following 8 queries:
SELECT option_value FROM wpstore_options WHERE option_name = '_transient_timeout_wpsc_theme_path' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = '_transient_wpsc_theme_path' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[productspage]%' AND `post_type` = 'page' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[shoppingcart]%' AND `post_type` = 'page' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[transactionresults]%' AND `post_type` = 'page' LIMIT 1 SELECT post_name FROM `wpstore_posts` WHERE `post_content` LIKE '%[userlog]%' AND `post_type` = 'page' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = '_transient_timeout_wpsc_url_wpsc-default.css' LIMIT 1 SELECT option_value FROM wpstore_options WHERE option_name = '_transient_wpsc_url_wpsc-default.css' LIMIT 1
So, first, let’s look at those transients. These transients work by caching where the WPEC theme files exist, to avoid having to check the stylesheet directory, then the template directory, then the default WPEC directory. There are two options to changing this: you can either head into your MySQL database and set the autoload value for these options to yes, or simply hardcode it. Personally, I know where these files are always going to live, so I went with hardcoding:
add_filter('pre_transient_wpsc_theme_path', array(__CLASS__, 'hardcode_wpsc_theme_path'));
add_filter('pre_transient_wpsc_url_wpsc-default.css', array(__CLASS__, 'hardcode_wpsc_theme_url'));
public function rm_hardcode_wpsc_theme_path($value) {
return WPSC_CORE_THEME_PATH;
}
public function rm_hardcode_wpsc_theme_url($value) {
return get_stylesheet_directory_uri() . '/wpsc-default.css';
}
We’ve now hardcoded most things and we’re down to four queries: the shortcode queries. Why does WPEC even need to look these up? Well, in order to create URLs for products, WPEC needs to know the base URL, which is set to the page where your productspage shortcode is set. There’s no easy way to get these, so it has to do a LIKE query across all of your pages. Doing this on each page load is a huge strain though (there is a bug filed about this though, so the developers are aware), especially given that we’re not going to be changing this often.
My favourite way to do this, as you may have noticed, is to hardcode it. Unfortunately, there are no filters on this, so you’ll need a custom patch to WPEC to add support for this. Essentially what the patch does is allow the page names to be set previously. I personally think that wp-config.php is the best place for these to live, but it’s your choice on where it is. Here’s what your code should look like:
global $wpsc_page_titles;
$wpsc_page_titles = array(
'products' => 'store',
'checkout' => 'checkout',
'transaction_results' => 'transaction-results',
'userlog' => 'your-account',
);
(The values should be set to the slug for each page respectively.)
Voilà, we’re down to zero queries from WPEC! This should minimise any extra stress on your MySQL server when it’s really not needed.
Sidenote: Some of these inefficiencies can be patched in WPEC, while others can’t be, due to the nature of hardcoding them. For those that can be patched, I’ll be attempting to work with the WPEC team to help them fix it. A quick site benefits everyone. 🙂
Edit: WordPress has __return_zero() built-in, thanks Rarst.