Skip to content
WP Intense Q & ACategory: Super Speedy SearchSearch speed optmisation
Steven W. asked 2 years 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 2 years 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

Your Answer


CYBERFUNDAY 40% discount coupon extended to Friday 9th December + join our Discord community and read the rules for an extra 20% discount coupon.