[wp-trac] [WordPress Trac] #19738: meta_query should check for wildcards when using LIKE operator

[WordPress Trac]

#19738: meta_query should check for wildcards when using LIKE operator
-------------------------+----------------------
 Reporter:  ejdanderson  |       Owner:
     Type:  enhancement  |      Status:  closed
 Priority:  normal       |   Milestone:
Component:  Query        |     Version:  3.2
 Severity:  normal       |  Resolution:  wontfix
 Keywords:               |     Focuses:
-------------------------+----------------------

Comment (by Will Brownsberger):

 Space's suggestion was helpful for me.  To get the full benefit, the
 following additional changes seemed necessary:

 (1) Add an index to wp_postmeta on the first 25 positions of the meta_key
 and meta_value columns.  This alone results in a huge improvement for like
 searches with only a trailing wildcard in direct sql queries, not through
 the wp_query object.
 (2) I found that WP_Query wraps meta casts the longtext metavalues as char
 like so: CAST(wp_postmeta.meta_value AS CHAR).  Apparently, this prevents
 the MYSQL optimizer from recognizing the opportunity to use the added
 index.  If the CAST wrapper is removed, the queries use the index and
 improve dramatically.

 I have a set of queries against a large set of meta_values and have
 enhanded the filter suggested above. I add the filter just before the big
 queries and then remove so as not to interfere with other queries:
 <code>  function remove_sql_wildcard_prefix($q) {
       $q['where'] = preg_replace("/(LIKE ')%(.*?%')/", "$1$2",
 $q['where']);
       $q['where'] = preg_replace("/(CAST\()(.*?)(.meta_value)( AS
 CHAR\))/", "$2$3", $q['where'] );
        return $q;
         }
 </code>

--
Ticket URL: <https://core.trac.wordpress.org/ticket/19738#comment:7>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform