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.