WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 2 years ago

#39878 closed enhancement (duplicate)

meta_form inner query is very slow for big post_meta tables

Reported by: eddr Owned by:
Milestone: Priority: normal
Severity: normal Version: 4.7.2
Component: Options, Meta APIs Keywords:
Focuses: Cc:

Description

  1. This query on every edit page:
    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
    

is really expensive.

  1. My site has big post_meta table as I'm using lots of custom fields (via advanced custom fields plugin).
  1. Removing the filters from the query, in my case, results in the same result set exactly
  1. There should be some option for built-in transient/cache of some sort for this specific one. This doesn't change almost at all, so flushing the cache won't be a problem
  1. The "order by" part takes really a lot in terms of performance and I'm not sure it is needed really. maybe a filter to allow the developer to change it?

Thanks

Change History (2)

#1 in reply to: ↑ description @eddr
2 years ago

Fixing and adding:

  1. Fix: My bad, the "order by" is not a performance issue
  2. Adding: my post_meta table has almost 200K rows. The query take around 0.3-0.35s on average and is the slowest by far - new VPS with 1 core and 2GB RAM on Linode, only one new site which is not public yet, so I consists 100% of the DB load.

#2 @SergeyBiryukov
2 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to duplicate
  • Status changed from new to closed

Hi @eddr, welcome to WordPress Trac!

Thanks for the report, we're already tracking this issue in #33885.

Note: See TracTickets for help on using tickets.