Speeding up Datafeedr Bulk Image Processing

I’m in the process of building a massive affiliate store. This affiliate store will have 500,000+ products in it and I’m going to use it to:

1. Make money (of course!)
2. Demo my Fast WooCommerce Filters (every WooCommerce category/attribute/price filter I’ve seen has awful performance on large stores and is the primary culprit for WooCommerce being slow at anything above 20,000 products) Update: This performance plugin is now available.
3. Demo Datafeedr (these guys make it really easy to build affiliate stores)
4. Demo my fast theme
5. Demo my Fast Ajax Product Search

So far I’ve loaded about 30,000 products (Update: now 820,000 products!). That’s large enough to see performance issues but small enough to not get annoyed while fixing them.

The first issue I ran into was that my shop category pages were slow (they shouldn’t be! Not with my plugins!). I installed the SQL Query Monitor plugin and found that there were hundreds of update_meta calls. Digging a little deeper, I found that Datafeedr is updating the images and downloading them on the fly. They do this to speed up the Datafeedr imports and effectively they’re loading the images just in time.

That’s not good for testing my filter speed however, so I ran the bulk image import. I started off by getting speeds of about 1 item processed every 3 – 6 seconds.

The server for this affiliate store is on Digital Ocean – it has a GB connection to the outside world and it uses SSD drives for storage, so fetching and saving an image should take a few milliseconds. I dug into the code and this is the query I found, at line 130-ish in plugins/datafeedr-product-sets/functions/ajax.php:

 $id = $wpdb->get_var( "
 SELECT pm1.post_id AS post_id 
 FROM $wpdb->postmeta AS pm1
 JOIN $wpdb->posts AS p
 ON p.ID = pm1.post_id
 LEFT JOIN $wpdb->postmeta AS pm2
 ON p.ID = pm2.post_id
 AND pm2.meta_key = '_thumbnail_id'
 LEFT JOIN $wpdb->postmeta AS pm3
 ON p.ID = pm3.post_id
 WHERE pm1.meta_key = '_dfrps_product_check_image'
 AND pm1.meta_value = '1'
 AND pm2.post_id IS NULL
 AND pm3.meta_key = '_dfrps_product_set_id'
 AND p.post_status = 'publish'
 ORDER BY post_id ASC
 " );

What that’s doing is checking to see which products don’t yet have an image saved against them.

Digging further, I found that the _dfrps_product_check_image value is actually set at the start of the job to process imports. That means I can change the query to this:

$id = $wpdb->get_var( ”
select pm1.post_id
FROM $wpdb->postmeta AS pm1
WHERE pm1.meta_key = ‘_dfrps_product_check_image’
AND pm1.meta_value = ‘1’
and exists (select * from $wpdb->posts p where p.ID = pm1.post_id AND p.post_status = ‘publish’)
limit 1;
” );

You can see the results and the bug this caused in this video:

The reason for the bug, I found, is that some rows may not be processed for some reason, yet they don’t get the value of _dfrps_product_check_image changed to 0 (to avoid them being reprocessed).

So, I added this line to the plugins/datafeedr-product-sets/classes/class-dfrps-image-importer.php file at line 86:

update_post_meta( $this->post->ID, '_dfrps_product_check_image', 0 ); // this needs to go here so we don't reprocess the same post over and over

Now, the Bulk Image Importer processes at least a dozen products per second as opposed to about a dozen per minute. That’s a 60-fold performance increase.

I’ve sent these edits over to Eric @ Datafeedr so they will be added to the next release of the plugin – in the meantime, if you wish to take advantage of this massive speed boost, go edit these two plugin files yourself and see the massive performance boost on your own site.

Here is the speed boost when I implemented this final solution:

Note: I also added these indexes to wp_posts and wp_postmeta (you can try with or without them – I prefer with, but the query is faster anyway and you might not like to edit the indexes on your underlying WordPress tables):

create index awdspeed1 on wp_posts(post_status, id);
create index awdspeed2 on wp_postmeta(meta_key,meta_value(1),post_id);
create index awdspeed3 on wp_postmeta(meta_key,post_id);

• Author
• Recent Posts

Chat to me

Dave Hilditch

Founder at Affiliate Web Designers

Dave has been programming since 6 years old and has been developing WordPress plugins, themes and websites since 2010. In the past he built the browse view technology for Skyscanner and now he helps clients with interesting website challenges.

He is always on at least one of his computers when he's awake, so get in touch and he'll get right back to you.

Chat to me

Latest posts by Dave Hilditch (see all)

• My WordPress performance plugins and server stack have moved - July 31, 2016
• Price Comparison Pro 1.2 Released - July 5, 2016
• How to run backups on huge WordPress websites without your website being brought offline - February 4, 2016