For the past few weeks I’ve been working on a WordPress plugin for importing a CSV into a few custom post types and taxonomies. I realized that sometimes going “The WordPress Way” isn’t actually the best way to do things. Occasionally, the quickest way to get stuff done is to dive into some SQL.

Every time the user uploads a CSV to be imported, I remove all of the old data. This means clearing all posts from the database. I started out using the WordPress function wp_delete_post() to accomplish this, but I’ve found that it can be EXTREMELY slow. It’s slow because its querying the database multiple times every time it runs.

If I’m deleting a post, wp_delete_post() runs at least 9 extra queries to delete things such as post meta, object relationships, and comments. This could potentially repeat for every revision of the post that exists too.

Since resources on the server are limited, I need to do this quickly and efficiently. If the posts that I’m trying to delete don’t have any of those other relationships or data associated then I don’t need to use the function. So I opted to skip all the extra work that WordPress does and wrote this SQL query:

$query = "
DELETE p,pm FROM $wpdb->posts p
JOIN $wpdb->postmeta pm ON p.ID = pm.post_id
WHERE p.post_type = 'provider'
OR p.post_type = 'practice';
";

$wpdb->query( $query );

This query is simple once you break it down.

It’s JOINING the wp_posts table with the wp_postmeta table using the post_id column that they both share. Therefore we can handle deleting the post AND the post meta using one query and it’s insanely fast.

Once I switched to using this query there was a noticeable jump in performance. What originally took close to 35-40 seconds now takes 1-2 and I’d say that’s a pretty good improvement!

NOTE: Writing SQL queries in WordPress is not always the best idea. WordPress functions handle other background processes that aren’t obvious and you should use those over straight SQL in almost all cases. If in doubt, use the recommended WordPress functions.