[wp-trac] [WordPress Trac] #32649: Index length optimization in 4.2 made one of the core queries unable to use it

[WordPress Trac]

#32649: Index length optimization in 4.2 made one of the core queries unable to use
it
--------------------------+-----------------------------
 Reporter:  viciousjack   |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Database      |    Version:  4.2.2
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 In 4.2 a few indexes got their length cut, which makes sense. But because
 of this particular case here:
 https://github.com/WordPress/WordPress/blob/974ca19e36dd71e7c506de05ca862d29a134e6dc
 /wp-admin/includes/upgrade.php#L2564

 (meta_key index in postmeta table)

 this core query can no longer use it:
 https://github.com/WordPress/WordPress/blob/974ca19e36dd71e7c506de05ca862d29a134e6dc
 /wp-admin/includes/template.php#L680

 The actual query that's executed:

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



 It needs to do full table traversal, and can do that inside index, but
 only if it's a full index.

 EXPLAIN results.

 Pre 4.2 :

 {{{
 SIMPLE  wp_postmeta     index   meta_key        meta_key        768
 NULL    336384  Using index
 }}}



 query finishes in 249 ms.

 Post 4.2:

 {{{
 SIMPLE  wp_postmeta     ALL     NULL    NULL    NULL    NULL    6793853
 Using temporary; Using filesort
 }}}

 query finishes in 46.1 seconds.  (~7kk rows in the table)

 MYSQL version 5.6.24

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