WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

Last modified 5 years ago

#32449 closed defect (bug) (duplicate)

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

Reported by: jeichorn Owned by:
Milestone: Priority: normal
Severity: major Version: 4.2
Component: Database Keywords:
Focuses: administration, performance Cc:

Description (last modified by dd32)

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?

Attachments (1)

template.php.patch.txt (767 bytes) - added by jeichorn 5 years ago.
Patch for wp-admin/includes/template.php

Download all attachments as: .zip

Change History (11)

@jeichorn
5 years ago

Patch for wp-admin/includes/template.php

#1 @johnbillion
5 years ago

  • Component changed from General to Posts, Post Types
  • Focuses administration added
  • Version changed from 4.2.2 to 4.2

See also / duplicate: #24498, #32361

#2 @jeichorn
5 years ago

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

#3 follow-up: @dd32
5 years ago

  • Component changed from Posts, Post Types to Database

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.

#4 @dd32
5 years ago

#32649 was marked as a duplicate.

#5 @dd32
5 years ago

#32581 was marked as a duplicate.

#6 in reply to: ↑ 3 @dd32
5 years ago

  • Description modified (diff)

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.

#7 follow-up: @jstensved
5 years ago

  • Focuses performance added
  • Severity changed from normal to major

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.

#8 in reply to: ↑ 7 @jeichorn
5 years ago

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.

#9 @swissspidy
5 years ago

  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #24498.

Let's concentrate efforts on one ticket.

#10 @netweb
5 years ago

  • Milestone Awaiting Review deleted
Note: See TracTickets for help on using tickets.