Last month I found myself dealing with random outages on one of the sites that I manage. At first I was convinced it was the holidays, then I quickly realized that there was a single query casing this mayhem. After hours of sifting through error_logs and watching my terminal as the sql passed by I found my issue. Oddly enough it was coming from a totally legitimate query that I would have never guessed would be the culprit.
Please keep in mind that this type of query is not dangerous, but in the current situation it became dangerous.
I have two post_types that need to be cross referenced for a query. Looking back I should have used a hidden associative custom taxonomy. At the time I decided to use post_meta to store associative term_id’s, which worked out well due to the method of post creation. Pulling the query seemed simple enough, and the amount of data in the query array was actually rather small.
The issue came from with in the processing of the meta_query. In the midst of processing this query there was a temporary copy of the data in order to pull the results. This temporary copy was casing a huge hangup and eventually the CPU% would spike and cause a db error.
One solution to this issue was to change the type of query I was running. I decided to pull the post id’s first using $wpdb->get_results() then use post__in with WP_Query() to get the actual posts.