wp-admin/includes/template.php meta_form query is causing full table scans in wordpress 4.2

[jeichorn]

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?

[jeichorn]

Patch for wp-admin/includes/template.php

[johnbillion]

See also / duplicate: #24498, #32361

[jeichorn]

I have verified that the updated query in #24498 does not improve performance in 4.2.

[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.

[dd32]

#32649 was marked as a duplicate.

[dd32]

#32581 was marked as a duplicate.

[dd32]

Replying to 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.

[jstensved]

I don't get any improvement from the patch but the table scan on wp_postmeta blocks the database and making one of my clients sites inaccessible in seconds.

I suggest this ticket should have higher severity. On larger sites with lots of traffic and big wp_postmeta this can kill the db in seconds.

It should definitely be tagged with performance in any circumstance.

[jeichorn]

Replying to jstensved:

I don't get any improvement from the patch but the table scan on wp_postmeta blocks the database and making one of my clients sites inaccessible in seconds.

I suggest this ticket should have higher severity. On larger sites with lots of traffic and big wp_postmeta this can kill the db in seconds.

It should definitely be tagged with performance in any circumstance.

If you drop the distinct from the patch it will solve your clients issues, it just reduces the changes of producing 30 items.

[swissspidy]

Duplicate of #24498.

Let's concentrate efforts on one ticket.