Why does my AJAX search/filter slow down with certain queries? WordPress
- REFUNDED
I got an AJAX taxonomy/date/search filter plugin built by a freelancer (links below), and for the most part it works—except that the certain form changes, especially the text search, cause it to slow down tremendously, and it can take 10, 20, 30 seconds or more to finish refreshing if you add/remove taxonomy terms or change the year slider after entering text.
The plugin code looks pretty inefficient—e.g., it repeats code for each taxonomy instead of making a class, it filters posts using MySQL statements instead of WP_Query query vars, and (I think) it calculates the number of results for each term by looping through the entire query for every single term (and there are at least a hundred terms in the form).
Can you take a look at the code and recommend how to fix it? Though there are potentially many things that could be improved with this code (and I'm interested to hear what they are), the specific problem I need to fix now is the loading speed issue.
<strong>Plugin files</strong>
https://gist.github.com/1168471 (a link to the live version is at the top of this gist)
https://gist.github.com/1168482 (ajax.php)
To replicate the problem, add some taxonomy terms/adjust the year slider. Then add search text and hit enter. Then try adding/removing terms again (or adjusting the date slider) and see how slow it becomes.
Answers (2)
Erez S answers:
Ask your hosting company to log your mysql slow queries. This logs should show you the slow queries you've excuted. So look at those logs and see if there is queries from this plugin.
In cPanel these logs are in the tmp/slow_queries, but I think you have to activate it first.
Erez S comments:
Anyway, as I can see, there is no loops that can cause this, so the problem is probably because of the long mysql query which take time.
Read these article in order to decrease the queries time:
http://20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/
web559 comments:
Do you not think that the getTermCountQuery function is a/the bottleneck?
Erez S comments:
I don't know exactly what is the problem, but if you will have the slow queries log, you'll be able to see what is the problematic query, and even what is the problematic part of the query.
Actually I don't know much about SQL databases, but what Gabriel said sounds right, so try to remove the full-text search part or the LIKE part and see if that improve the load time.
If not than this is something else which you'll be able to see in the slow queries log.
Gabriel Reguly answers:
Hi web559,
Your querys are using 'LIKE %' for full-text search.
That is a no-no in speed terms.
I can fix that for you, but not today.
Also, as you already know, your ajax is not very WordPress friendly.
You can get some tips [[LINK href="http://www.garyc40.com/2010/03/5-tips-for-using-ajax-in-wordpress/"]]here[[/LINK]]
Regards,
Gabriel
web559 comments:
Yes, I suspected that the LIKE search was hugely inefficient. Would it improve thing substantially to changed that aspect from MySQL the Wordpress search var ('s' => $search_text)
And, is it even possible to "mix" standard WP_Query query_vars with custom $wp_query->request?
(This is a key question, I think, because as far as I know there's no way to restrict results to a date range in WP_Query without a custom MySQL query -- http://codex.wordpress.org/Class_Reference/WP_Query#Time_Parameters)