[wp-trac] [WordPress Trac] #32449: wp-admin/includes/template.php meta_form query is causing full table scans in wordpress 4.2

[WordPress Trac]

#32449: wp-admin/includes/template.php meta_form query is causing full table scans
in wordpress 4.2
--------------------------+------------------------------
 Reporter:  jeichorn      |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  4.2
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  administration
--------------------------+------------------------------

Old description:

> It looks like the changes to indexes for utf8_mb4 is breaking this query.
>
> SELECT meta_key
>                 FROM $wpdb->postmeta
>                 GROUP BY meta_key
>                 HAVING meta_key NOT LIKE %s
>                 ORDER BY meta_key
>                 LIMIT %d";
>
> Its always doing a full table scan now.  On a site with 7 million rows in
> postmeta this takes a long time.
>
> I haven't been able to come up with a solution to force mysql to use the
> index.
>
> I've included a patch fixes the performance problem, but i'm not sure how
> much use this feature is in general.  On the site in question there are
> 10k distinct meta keys that don't start with _, what does showing a
> random 30 of them buy anyone?

New description:

 It looks like the changes to indexes for utf8_mb4 is breaking this query.

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

 Its always doing a full table scan now.  On a site with 7 million rows in
 postmeta this takes a long time.

 I haven't been able to come up with a solution to force mysql to use the
 index.

 I've included a patch fixes the performance problem, but i'm not sure how
 much use this feature is in general.  On the site in question there are
 10k distinct meta keys that don't start with _, what does showing a random
 30 of them buy anyone?

--

Comment (by dd32):

 Replying to [comment:3 dd32]:
 > > I have verified that the updated query in #24498 does not improve
 performance in 4.2.
 >
 > I'm seeing the opposite, it appears to restore the index usage, and is
 faster.

 I take that back, while it does restore the usage of the index for me,
 it's not significantly faster.

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