Post and post meta query slow not fetch data takes approx 390 sec

[aloktiwari]

Hello,

I am trying to fetch the data post using post meta join queries. But it is not fetching fast taking 356 sec or more like as 19 mins many times.
below the array for query_posts =>

Array
(
    [post_type] => for_sale
    [post_status] => publish
    [meta_query] => Array
        (
            [0] => Array
                (
                    [key] => residential
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [1] => Array
                (
                    [key] => commercial
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [2] => Array
                (
                    [key] => land
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [3] => Array
                (
                    [key] => all
                    [value] => a
                    [compare] => LIKE
                    [type] => CHAR
                )

            [4] => Array
                (
                    [key] => listing_id
                    [value] => 12
                    [compare] => LIKE
                    [type] => CHAR
                )

            [5] => Array
                (
                    [key] => owner_financing
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [6] => Array
                (
                    [key] => foreclosure
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [7] => Array
                (
                    [key] => income_producing
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [8] => Array
                (
                    [key] => close_beach
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [9] => Array
                (
                    [key] => views
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [10] => Array
                (
                    [key] => price
                    [value] => 0-10000000
                    [compare] => LIKE
                    [type] => CHAR
                )

            [11] => Array
                (
                    [key] => numacreage
                    [value] => 0-10000000
                    [compare] => LIKE
                    [type] => CHAR
                )

            [12] => Array
                (
                    [key] => new
                    [value] => 1
                    [compare] => LIKE
                    [type] => CHAR
                )

            [13] => Array
                (
                    [key] => availability
                    [value] => Sold
                    [compare] => LIKE
                    [type] => CHAR
                )

            [14] => Array
                (
                    [key] => neighborhood
                    [value] => Esperanza
                    [compare] => LIKE
                    [type] => CHAR
                )

            [15] => Array
                (
                    [key] => property_type
                    [value] => Residential_Lot
                    [compare] => LIKE
                    [type] => CHAR
                )

            [16] => Array
                (
                    [key] => documents
                    [value] => is_Titled
                    [compare] => LIKE
                    [type] => CHAR
                )

            [17] => Array
                (
                    [key] => zoning
                    [value] => Residential_Use
                    [compare] => LIKE
                    [type] => CHAR
                )

        )

    [paged] => 0
    [posts_per_page] => 5
)

Also i am trying to change the custom query like as "SELECT wposts.*
FROM wp_posts wposts, wp_postmeta pt, wp_postmeta pm, wp_postmeta pl, wp_postmeta ps, wp_postmeta cm, wp_postmeta li, wp_postmeta of
WHERE wposts.ID = pt.post_id
AND wposts.ID = pm.post_id
AND wposts.ID = pl.post_id
AND wposts.ID = ps.post_id
AND wposts.ID = cm.post_id
AND wposts.ID = li.post_id
AND wposts.ID = of.post_id
AND (pt.meta_key = 'documents' AND pt.meta_value = 'is_Titled')
AND (pm.meta_key = 'neighborhood' AND pm.meta_value = 'Esperanza')
AND (pl.meta_value = 'Residential_Use' AND pl.meta_key='zoning' )
AND (ps.meta_value = '1' AND ps.meta_key='residential' )
AND (cm.meta_value = '1' AND cm.meta_key='commercial' )
AND (li.meta_value = '12' AND li.meta_key='listing_id' )
AND (of.meta_value = '1' AND of.meta_key='owner_financing' )
AND wposts.post_type IN ('post', 'page', 'attachment', 'for_sale', 'vacation_rental')
AND (wposts.post_type = 'for_sale')
AND wposts.post_status = 'publish'
GROUP BY wposts.ID DESC"

This also very very slow. Please help it's urgent. Postmeta have large data.

Thanks
in advance

[boonebgorges]

Hi aloktiwari - LIKE queries against a longtext column like meta_value are always going to be fairly slow. And combining large tables with JOINs is always going to cause performance issues. You are doing both. There's not much that WP can do to improve this.

My suggestions are as follows:

• Stop using 'compare=LIKE'. It looks like you want to do strict comparisons anyway, so use 'compare' => '='. This alone should improve performance quite a bit.
• Use taxonomies instead of postmeta. I'm guessing that your data here is part of a number of controlled taxonomies. This seems more like "category" or "tag" type of data. Queries that use tax_query are going to be much faster than those using meta_query.
• If you need true search capabilities at scale, consider an external search appliance like Solr or ElasticSearch.