Search speed optmisation

WP Intense Q & ACategory: Super Speedy SearchSearch speed optmisation
Steven Warwick asked 3 weeks ago

Hi there,

I've just installed and configured Super Speedy Search and I'm wondering if I need to maybe to tweak some setting to improve the speed a bit more. A test search for "Apple ipad" for example just took 28sec for the query below to run. Any suggestions are greatly appreciated and a massive thanks for your other plugins too, External Images in particular has been amazing in order to offload to S3.

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID

FROM wp_posts

INNER JOIN wp_postmeta

ON ( wp_posts.ID = wp_postmeta.post_id )

LEFT JOIN wp_term_relationships estr

ON (wp_posts.ID = estr.object_id)

LEFT JOIN wp_term_taxonomy estt

ON (estr.term_taxonomy_id = estt.term_taxonomy_id)

LEFT JOIN wp_terms est

ON (estt.term_id = est.term_id)

WHERE 1=1

AND ( wp_posts.ID NOT IN (

SELECT object_id

FROM wp_term_relationships

WHERE term_taxonomy_id IN (7,10) ) )

AND ((MATCH(wp_posts.post_title,wp_posts.post_content,wp_posts.post_excerpt) AGAINST('+apple +ipad' IN BOOLEAN MODE))

OR (wp_posts.ID IN (select post_id from wp_postmeta espm

WHERE MATCH(espm.meta_value) AGAINST('+apple +ipad' IN BOOLEAN MODE)

AND espm.meta_key = '_sku'))

OR ( (estt.taxonomy = 'product_cat'

AND est.name LIKE '%apple%')

OR (estt.taxonomy = 'pa_brand'

AND est.name LIKE '%apple%'))

OR ( (estt.taxonomy = 'product_cat'

AND est.name LIKE '%ipad%')

OR (estt.taxonomy = 'pa_brand'

AND est.name LIKE '%ipad%')))

AND ( ( wp_postmeta.meta_key = '_stock_status'

AND wp_postmeta.meta_value = 'instock' ) )

AND wp_posts.post_type = 'product'

AND ((wp_posts.post_status = 'publish'))

AND exists (select * from wp_postmeta pm where wp_posts.id = pm.post_id and pm.meta_key = '_stock_status' and pm.meta_value = 'instock')

GROUP BY wp_posts.ID

ORDER BY (CASE

WHEN wp_posts.post_title LIKE '%apple ipad%'

THEN 1

WHEN wp_posts.post_title LIKE '%apple%'

AND wp_posts.post_title LIKE '%ipad%'

THEN 2

WHEN wp_posts.post_title LIKE '%apple%'

OR wp_posts.post_title LIKE '%ipad%'

THEN 3

WHEN wp_posts.post_excerpt LIKE '%apple ipad%'

THEN 4

WHEN wp_posts.post_content LIKE '%apple ipad%'

THEN 5

ELSE 6 END), wp_posts.post_date DESC

LIMIT 0, 9

Steven W. replied 3 weeks ago

I didn’t mean to redact the tables, here’s the original query:

SELECT SQL_CALC_FOUND_ROWS DISTINCT wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
LEFT JOIN wp_term_relationships estr
ON (wp_posts.ID = estr.object_id)
LEFT JOIN wp_term_taxonomy estt
ON (estr.term_taxonomy_id = estt.term_taxonomy_id)
LEFT JOIN wp_terms est
ON (estt.term_id = est.term_id)
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7,10) ) )
AND ((MATCH(wp_posts.post_title,wp_posts.post_content,wp_posts.post_excerpt) AGAINST(‘+apple* +ipad*’ IN BOOLEAN MODE))
OR (wp_posts.ID IN (select post_id from wp_postmeta espm
WHERE MATCH(espm.meta_value) AGAINST(‘+apple* +ipad*’ IN BOOLEAN MODE)
AND espm.meta_key = ‘_sku’))
OR ( (estt.taxonomy = ‘product_cat’
AND est.name LIKE ‘%apple%’)
OR (estt.taxonomy = ‘pa_brand’
AND est.name LIKE ‘%apple%’))
OR ( (estt.taxonomy = ‘product_cat’
AND est.name LIKE ‘%ipad%’)
OR (estt.taxonomy = ‘pa_brand’
AND est.name LIKE ‘%ipad%’)))
AND ( ( wp_postmeta.meta_key = ‘_stock_status’
AND wp_postmeta.meta_value = ‘instock’ ) )
AND wp_posts.post_type = ‘product’
AND ((wp_posts.post_status = ‘publish’))
AND exists (select * from wp_postmeta pm where wp_posts.id = pm.post_id and pm.meta_key = ‘_stock_status’ and pm.meta_value = ‘instock’)
GROUP BY wp_posts.ID
ORDER BY (CASE
WHEN wp_posts.post_title LIKE ‘%apple ipad%’
THEN 1
WHEN wp_posts.post_title LIKE ‘%apple%’
AND wp_posts.post_title LIKE ‘%ipad%’
THEN 2
WHEN wp_posts.post_title LIKE ‘%apple%’
OR wp_posts.post_title LIKE ‘%ipad%’
THEN 3
WHEN wp_posts.post_excerpt LIKE ‘%apple ipad%’
THEN 4
WHEN wp_posts.post_content LIKE ‘%apple ipad%’
THEN 5
ELSE 6 END), wp_posts.post_date DESC
LIMIT 0, 9

Subscribe!

We publish performance optimisation guides regularly.

Subscribe so you never miss out.

We use cookies

We use cookies for various things on our site, including our on-site chat bubble (if you use it), our comment forms (if you use them) and for session handling (if you log in).

Other than that, we use cookies to identify where traffic came from to help us understand which traffic turns into sales, we use a cookie for Google Analytics traffic analysis and we use a cookie to customise adverts for our own products we think you'll be interested in.

You can read more detail in our privacy policy page. Please click 'Accept' or 'Decline' to continue.