[wp-trac] [WordPress Trac] #31595: Post and post meta query slow not fetch data takes approx 390 sec

[WordPress Trac]

#31595: Post and post meta query slow not fetch data takes approx 390 sec
--------------------------+-----------------------------
 Reporter:  aloktiwari    |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 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

--
Ticket URL: <https://core.trac.wordpress.org/ticket/31595>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform