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

[vadikcoma]

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?

[earnjam]

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/

[vadikcoma]

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

Vadim