Question about a query in JR
I was contacted by my web hosting provider (now former web hosting provider) a while ago and they complained about one really heavy DB query generated by JobRoller. According to the hosting provider, this query actually affected the DB performance heavily and affected other clients.
This query looks like this and is located in the theme-fuctions.php:
SELECT ID FROM wp_jobroller_posts WHERE ID NOT IN ( SELECT wp_jobroller_postmeta.post_id FROM wp_jobroller_postmeta WHERE meta_key = 'geo_short_address' ) AND wp_jobroller_posts.post_status = 'publish')';
I put my site in to maintenance mode and tried to run the query in phpmyadmin and YES, it took about 10 seconds to run it every time.
However, I THINK there’s a really quick workaround to reduce the query time a lot, just by changing the order of the query to this:
SELECT ID FROM $wpdb->posts WHERE $wpdb->posts.post_status = 'publish' AND ID NOT IN ( SELECT $wpdb->postmeta.post_id FROM $wpdb->postmeta WHERE meta_key = 'geo_short_address' )';
Now this query generates an answer in 0.5 seconds compared to 10 seconds before and you’re basically asking the DB exactly the same thing.
Is this maybe a solution the developers could implement in future versions of JR?
My JR site has:
About 120 published job ads and about 1 300 private.
About 15 000 visits / month
About 45 000 page views / month.