Slow queries generated by Zoninator

[gudmdharalds]

We have seen slow queries being generated by Zoninator, in particular a query that looks like this:

SELECT wp_posts.ID FROM wp_posts WHERE ?=? AND ( wp_posts.post_date > ?) AND wp_posts.ID NOT IN (?, ?, ?, ?, ?, ?, ?, ?) AND (((wp_posts.post_title LIKE ?) OR (wp_posts.post_excerpt LIKE ?) OR (wp_posts.post_content LIKE ?))) AND wp_posts.post_type = ? AND ((wp_posts.post_status = ? OR wp_posts.post_status = ?)) ORDER BY wp_posts.post_date DESC LIMIT ?, ?

It is in particular the NOT IN part that causes issues, as when data grows in wp_posts, there is more to exclude when processing the query. This can lead the database to overload, causing sites to have performance issues.

Is there any way to rewrite this without the NOT IN?

---

This issue originated as a VIP Request.

[rogertheriault]

Even without the NOT IN clause, this can be rather slow and problematic on sites with a lot of posts.

It's compounded when the user is actively typing, there are 2 or more words, and the typeahead generates additional requests while the original ones are still in process

SELECT wp_posts.ID FROM wp_posts
WHERE 1=1 AND 
( wp_posts.post_date > XXX )
AND (((wp_posts.post_title LIKE XXX) OR (wp_posts.post_excerpt LIKE XXX) OR (wp_posts.post_content LIKE XXX))
AND ((wp_posts.post_title LIKE XXX) OR (wp_posts.post_excerpt LIKE XXX) OR (wp_posts.post_content LIKE XXX))
AND ((wp_posts.post_title LIKE XXX) OR (wp_posts.post_excerpt LIKE XXX) OR (wp_posts.post_content LIKE XXX)))
AND wp_posts.post_type = XXX
AND ((wp_posts.post_status = XXX OR wp_posts.post_status = XXX))
ORDER BY wp_posts.post_date DESC LIMIT XXX, XXX

[rogertheriault]

Can zoninator perhaps use ES search for these searches (which come through admin-ajax) when it's available?

[alvinchevolleaux]

Is there a way to offload Zoninator queries to ES yet? We're facing issues with the search also.