Search for custom fields and tags in Wordpress with delay of results

1

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%')
            )

        )
    
asked by anonymous 14.08.2015 / 18:44

0 answers