I have a Wordpress website that has custom fields related to "Photo Albums", which I am now creating a search engine for.
The problem is that because it uses Tags in its albums and custom_fields in the photos, when I perform a search where all the search must be between one, another or both, the search delay results from 30 to 40 seconds.
Tables: wp_terms with 688 records wp_term_taxonomy with 752 records wp_term_realtionships with 4511 records wp_postmeta with 629459 records wp_post with 9929 records
I would like help to optimize this my subquery, which takes the delay in return.
SELECT
t1.post_id,
t1.meta_value,
t1.meta_id,
CONCAT(
t1.meta_value,
GROUP_CONCAT(wt. NAME SEPARATOR ',')
) AS conc_value,
REPLACE (
t1.meta_key,
'image',
'details'
) AS details,
REPLACE (
t1.meta_key,
'type',
'details'
) AS details2,
REPLACE (
t1.meta_key,
'details',
'image'
) AS details3,
REPLACE (t1.meta_key, 'image', 'type') AS type,
REPLACE (
t1.meta_key,
'details',
'type'
) AS type2,
REPLACE (t1.meta_key, 'type', 'image') AS type3
FROM
wp_postmeta t1
INNER JOIN wp_posts t2 ON t1.post_id = t2.ID
AND t2.post_type = 'post'
INNER JOIN wp_term_relationships wtr ON t2.id = wtr.object_id
INNER JOIN wp_term_taxonomy wtt ON wtt.term_taxonomy_id = wtr.term_taxonomy_id
AND wtt.taxonomy = 'post_tag'
INNER JOIN wp_terms wt ON wt.term_id = wtt.term_id
GROUP BY
t1.meta_id
HAVING /*OS CAMPOS AQUI SÃO VARIAVEIS, ESTE CONTEM 3 POREM NO MEU CODIGO PHP EU PERMITO ATE 6 POR CONTA DA REGRA DE NEGOCIO ESTABELECIDA*/
(
(
BINARY CONVERT (
UCASE(conc_value) USING utf8
) LIKE BINARY UCASE(_utf8 '%religião%')
)
AND
(
BINARY CONVERT (
UCASE(conc_value) USING utf8
) LIKE BINARY UCASE(_utf8 '%pontos%')
)
AND(
BINARY CONVERT (
UCASE(conc_value) USING utf8
) LIKE BINARY UCASE(_utf8 '%centro%')
)
AND(
BINARY CONVERT (
UCASE(conc_value) USING utf8
) LIKE BINARY UCASE(_utf8 '%corrente%')
)
)