WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#46709 closed defect (bug) (invalid)

AND ( mx_postmeta.meta_key = 'post_views_count' ) called when there is no need to

Reported by: vadikcoma Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Database Keywords:
Focuses: Cc:

Description

Hello, I might be in a wrong place, but we are totally lost. We are optimizing a large WordPress website and came across this issue:

SELECT SQL_CALC_FOUND_ROWS mx_posts.ID
FROM mx_posts 
INNER JOIN mx_postmeta
ON ( mx_posts.ID = mx_postmeta.post_id )
WHERE 1=1 
AND ( ( YEAR( mx_posts.post_date ) = 2019
AND WEEK( mx_posts.post_date, 1 ) = 13 ) )
AND ( mx_postmeta.meta_key = 'post_views_count' )
AND mx_posts.post_type = 'post'
AND ((mx_posts.post_status = 'publish'))
GROUP BY mx_posts.ID
ORDER BY mx_postmeta.meta_value+0 DESC
LIMIT 1, 10

This query takes around 1 second to process on 500.000+ article database. Homepage takes around 15 seconds to load due to similar requests.

Is it a WordPress bug that it starts running really slow on large sites?

Attachments (2)

Screenshot (80).png (270.8 KB) - added by vadikcoma 2 years ago.
Screenshot (79).png (265.7 KB) - added by vadikcoma 2 years ago.

Download all attachments as: .zip

Change History (4)

#1 @earnjam
2 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed
  • Version 5.1 deleted

Hi @vadikcoma and welcome to WordPress Trac!

Postmeta queries can be slow due to lack of indexing on the meta_value column. When you have to join those across large databases, as you've seen, performance can be pretty bad.

In this case, it's not an issue with WordPress Core. Your theme is making these calls, so you should find a way to optimize the theme's code, probably either through adjustments to the queries or some sort of caching.

WordPress Trac is used for tracking bugs and enhancements for WordPress core, so I'm going to close this ticket. The best place to follow up for additional help would be the support forums at https://wordpress.org/support/forums/

#2 @vadikcoma
2 years ago

Thank you very much! We will keep working on it,

Vadim

Note: See TracTickets for help on using tickets.