Slow query for search

[optimalpartnersParticipant]

Hello
We are having trouble with speed using ajax search pro.
The slow query is this one:
SELECT asp_index.doc as id, asp_index.blogid as blogid, ‘pagepost’ as content_type, 100 AS priority, 1 AS p_type_priority, 0 AS post_author, 1 as customfp, 1 as customfs, asp_index.post_type AS post_type, ( asp_index.title * 100 * 10 + asp_index.content * 10 * 10 + asp_index.excerpt * 100 * 10 + asp_index.comment * 3 * 10 + asp_index.link * 3 * 10 + asp_index.tag * 3 * 10 + asp_index.customfield * 10 * 10 + asp_index.author * 2 * 10 ) AS relevance FROM wp_asp_index as asp_index WHERE asp_index.term LIKE ‘university%’ AND (asp_index.post_type IN (‘articles’,’resources’,’web-resource’,’wprss_feed_item’,’peer’,’consultant’,’page’) ) AND asp_index.blogid = 1 AND (( ( NOT EXISTS ( SELECT * FROM wp_term_relationships as xt INNER JOIN wp_term_taxonomy as tt ON ( xt.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = ‘wprss_category’) WHERE xt.object_id = asp_index.doc ) OR asp_index.doc IN ( SELECT DISTINCT(tr.object_id) FROM wp_term_relationships AS tr LEFT JOIN wp_term_taxonomy as tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = ‘wprss_category’) WHERE tt.term_id NOT IN (61) AND tr.object_id NOT IN ( SELECT DISTINCT(trs.object_id) FROM wp_term_relationships AS trs

It takes almost 500 ms and is done several times per search adding up to several seconds to respond.

Is there any setting we can change to make this faster?
I have been tuning mysql but there is not much more I can do there.
with best regards
Mario Flores

[Ernest MarcinkoKeymaster]

Hi,

Despite of the query length, this is very highly optimized. Have you run this single query manually on the database and it took 500ms to respond? I’m asking because I have manually tested these queries in depth (with query explanations and logs) for more than a month before release, and the final results from the database rarely took more than 20 ms to respond, even on a server with over 100k+ records – even more complex ones with custom fields and such. The short answer is, that I don’t think that it is possible to optimize this query further, in any ways.
If the response time is in fact that high, then I recommend checking/restarting the database server first to make sure it’s not some process stuck in there.

As an example, this server runs on a 10$/month VPS, and the index table is 255 920 rows, and the query above (not the exact copy, but same equivalent) executed in 2.6 milliseconds, returning 1792 rows: https://i.imgur.com/UNSdMXX.png
Repetitive queries are even faster.

More in-depth explanation: This query is executed on the index table, with a subquery on the taxonomy terms table, excluding objects from term 61. Notice that the “LIKE” query part is respecting the rules to not break the indexing, to bypass the much slower operation. The slowest part is actually this term exclusion, as it needs to join the terms table with the term relationships table. There is no way of bypassing that, as there is no single table containing post type relationships and term relationships exclusively. The wp_term_relationships needs an extra join to be able to tell if the object is in fact the the object typre requested and not something else.
If there was a table containing only post type object relations to terms, this could be optimized even further, using only a sub-select query on that single table.

Best,
Ernest Marcinko
If you like my products, don't forget to rate them on codecanyon :)

[optimalpartnersParticipant]

Thank you for the response. Yes, wordpress was not made to deal with very large data sets. Our wp_asp_index table as 3 500 000 records. It is a lot.

We are also having problems with another query in related posts plugin:

SELECT
wp_posts.post_title as title,
wp_posts.ID as id,
wp_posts.post_date as date,
wp_posts.post_content as content,
wp_posts.post_excerpt as excerpt,
” as author,
wp_posts.post_type as post_type,
(SELECT DISTINCT GROUP_CONCAT( wp_term_taxonomy.term_id SEPARATOR ‘,’ ) FROM wp_term_taxonomy
LEFT JOIN wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
WHERE (wp_term_relationships.object_id = wp_posts.ID) ) as ttid,
((case when
(wp_posts.post_title LIKE ‘%yammer%’)
then 10 else 0 end) + (case when
(wp_posts.post_excerpt LIKE ‘%social%’)
then 6 else 0 end)) as relevance
FROM wp_posts
LEFT JOIN wp_postmeta ON wp_postmeta.post_id = wp_posts.ID

WHERE
(wp_posts.ID <> 162672) AND
(wp_posts.post_type IN (‘dummy_notting’,’post’,’page’,’articles’,’resources’,’wprss_feed_item’,’web-resource’,’peer’,’consultant’)) AND
(
NOT EXISTS (SELECT * FROM wp_term_relationships as xt WHERE xt.object_id = wp_posts.ID)
OR
wp_posts.ID IN (
SELECT DISTINCT(tr.object_id)
FROM wp_term_relationships AS tr
LEFT JOIN wp_term_taxonomy as tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
WHERE
tt.term_id NOT IN (61)
AND tr.object_id NOT IN (
SELECT DISTINCT(trs.object_id)
FROM wp_term_relationships AS trs
LEFT JOIN wp_term_taxonomy as tts ON trs.term_taxonomy_id = tts.term_taxonomy_id
WHERE tts.term_id IN (61)
)
)
) AND
(1) AND
((1)) AND
wp_posts.ID NOT IN (1070, 815, 1345, 1319, 33753, 34547) AND
(wp_posts.post_status = ‘publish’) AND
(( wp_postmeta.meta_key=’wpcf-special-excerpt’ AND wp_postmeta.meta_value LIKE ‘%social%’ OR wp_postmeta.meta_value LIKE ‘%network%’ ) OR ( wp_posts.post_title LIKE ‘%yammer%’ OR wp_posts.post_title LIKE ‘%enterprise%’ OR wp_posts.post_title LIKE ‘%social%’ ) OR ( wp_posts.post_excerpt LIKE ‘%social%’ OR wp_posts.post_excerpt LIKE ‘%network%’ ))
GROUP BY wp_posts.ID
ORDER BY relevance DESC, wp_posts.post_date DESC
LIMIT 7

This one, using explain in mysql, give me a first select with +70 000 results.

So, I believe there is not much that can be done here.

Thank you again for the assistance.

[Author]

Posts