[wp-trac] [WordPress Trac] #24498: Improving performance of meta_form()

[WordPress Trac]

#24498: Improving performance of meta_form()
----------------------------+--------------------------
 Reporter:  lumaraf         |       Owner:  pento
     Type:  enhancement     |      Status:  assigned
 Priority:  normal          |   Milestone:  4.3
Component:  Administration  |     Version:  3.5
 Severity:  normal          |  Resolution:
 Keywords:  has-patch       |     Focuses:  performance
----------------------------+--------------------------

Comment (by rarylson):

 Hi @pento,

 The real optimization is using the WHERE (exclusions before sorting)
 instead of only using HAVING (exclusions after sorting).

 About the query proposed in the patch:

 {{{
 SELECT DISTINCT meta_key FROM wp_postmeta
 WHERE meta_key NOT BETWEEN '_' AND '_z'
 HAVING meta_key NOT LIKE '\_%'
 ORDER BY meta_key
 LIMIT 30
 }}}

 This patch runs an exclusion, after a sort, and after it runs an other
 exclusion.

 The other one (comments 10 and 11) runs an exclusion and after a sort
 (there is no need of running another exclusion after the sort, because
 there is no HAVING clause).

 {{{
 SELECT DISTINCT meta_key FROM wp_postmeta
 WHERE meta_key NOT LIKE '\_%'
 ORDER BY meta_key
 LIMIT 30
 }}}

 The execution plan is very similar in both cases. Both of them are
 practically equivalent in terms of efficient.

 It's possible that excluding using a 'not in range' logic be a bit quicker
 that excluding using a 'not like' logic. But it will only make difference
 if there is no meta_key entry in the form _X, where X is a character
 greater than z. For example, '_Á' or '_ß'. In these cases, the second
 query would run in the same performance or even a little faster then the
 first one. More over, the `NOT BETWEEN` performance depends on the used
 CHARSET and its results depend on the COLLATION. It's possible that for
 some cases the first query will be better and for others the second query
 will be better.

 So, in this specific case, **I prefer the simpler solution (comments
 10/11)**. But I remember that both of them are correct and much more
 efficient than the original one.

 Final note:

 The first query could be rewritten also as:

 {{{
 SELECT DISTINCT meta_key FROM wp_postmeta
 WHERE meta_key NOT BETWEEN '_' AND '_z'
     AND meta_key NOT LIKE '\_%'
 ORDER BY meta_key
 LIMIT 30
 }}}

 This moves the final exclude (after the sort) to before the sort.
 Technically, it will be a little better.

 It's also worth to notice that using the next query is wrong (because the
 '_Á' and '_ß' bug and the problem of changing the COLATION). So, the NOT
 LIKE clause (after or before the sort) is still required.

 {{{

 SELECT DISTINCT meta_key FROM wp_postmeta
 WHERE meta_key NOT BETWEEN '_' AND '_z'
 ORDER BY meta_key
 LIMIT 30
 }}}

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