[wp-trac] [WordPress Trac] #53450: [WP_Meta_Query] Add faster LIKE based 'STARTSWITH' and 'ENDSWITH' compare modes for value query

[WordPress Trac]

#53450: [WP_Meta_Query] Add faster LIKE based 'STARTSWITH' and 'ENDSWITH' compare
modes for value query
-------------------------+-------------------------------------------------
 Reporter:  janthiel     |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Query        |    Version:  trunk
 Severity:  normal       |   Keywords:  dev-feedback has-patch needs-docs
  Focuses:  performance  |  needs-codex has-unit-tests
-------------------------+-------------------------------------------------
 Currently the "LIKE" compare mode for meta value compares is only usable
 for ''contains'' queries as it always adds wildcards around all queries
 {{{LIKE '%query%'}}}. This makes one use the more complex REGEXP compare
 mode for queries which easily could be written with {{{LIKE '%query'}}} or
 {{{LIKE 'query%'}}}.

 As LIKE is faster than REGEXP it is preferable to use LIKE.
 See: http://billauer.co.il/blog/2020/12/mysql-index-pattern-matching-
 performance/

 In addition people don't have to use the much more complex regex. Which
 tends to introduce errors in my experience as most people just copy &
 paste but do not understand how regex really works (not meant as an
 offense). So REGEXP should be avoided if possible.

 I would suggest naming the new compare types {{{STARTSWITH}}} and
 {{{ENDSWITH}}}. Also adding their {{{NON ...}}} counter parts to match up
 the ''LIKE'' behaviour.

 **Maybe** also add an alias for ''LIKE'' named {{{CONTAINS}}} as the
 current naming ''LIKE'' suggests that you could pass in the wildcards
 yourself. Which is not the case and thus misleading. But this is just for
 the sake of the tickets completenes. The pull request only contains code
 and tests for the new modes.

 As an alternative I thought about reworking the current LIKE mode to allow
 custom wildcard passing. But this will clearly break backward compat and
 thus I discarded this approach.

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