[wp-trac] [WordPress Trac] #36625: Don't CAST the post meta value to CHAR in meta query

[WordPress Trac]

#36625: Don't CAST the post meta value to CHAR in meta query
-------------------------+------------------------------
 Reporter:  ericlewis    |       Owner:
     Type:  enhancement  |      Status:  new
 Priority:  normal       |   Milestone:  Awaiting Review
Component:  Query        |     Version:  3.1
 Severity:  normal       |  Resolution:
 Keywords:               |     Focuses:
-------------------------+------------------------------

Comment (by ericlewis):

 Replying to [comment:1 boonebgorges]:
 > Can we get some more technical details on how the optimizer decides
 whether the index can be used?

 The index is the column value's leftmost prefix, truncated to the index's
 length. The optimizer consider using the index except in some cases:

 * a function is applied to the index column's value in a where clause
 (i.e. `CHAR(postmeta.meta_value)`.
 * a `LIKE` clause is used on the index column with a wildcard on the left-
 end of the search string (i.e. `postmeta.meta_value LIKE "%erosmith"`).

 There may be other cases an index would not be considered for use by the
 optimizer, or a better general comment on when indexes are considered for
 use (@barry, @pento ?)

 > What happens when your index is 8 characters, and the `meta_value` from
 your `meta_query` is 12 characters?

 If the query is `meta_value = "Aerosmith"`, and `meta_value` has an index
 with an 8 character length, MySQL would use the index to figure out
 possible rows to touch in the actual table (the "Aerosmith" row being one
 of them), and rule out rows to touch in the actual table (i.e. a row with
 a `meta_value` of `Van Halen`) based on partial string matches.

 > (These are genuine questions - I don't know, and I'm having a hard time
 finding documentation about it.)

 These are great questions I'm not finding answer to in the MySQL reference
 either. I opened [http://bugs.mysql.com/bug.php?id=81181&thanks=4
 mysql#81181] to discuss updating the reference with more detail here.

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