Make WordPress Core

Opened 20 months ago

Last modified 20 months ago

#58593 new enhancement

Slow list of posts in the admin panel (with a large wp_posts table)

Reported by: shoorf's profile Shoorf Owned by:
Milestone: Awaiting Review Priority: normal
Severity: normal Version: 6.2.2
Component: Query Keywords:
Focuses: administration, performance Cc:

Description

Hello. With a large wp_posts table (more than 1 GB, more than 100k published posts), the list of posts in the admin panel may open for several seconds. Slow SQL query that calculates the number of "my" posts:

SELECT COUNT( 1 )
FROM wp_posts
WHERE post_type = '<post_type>'
AND post_status NOT IN ( 'trash','auto-draft','inherit','request-pending','request-confirmed','request-failed','request-completed' )
AND post_author = 1

Solution:

ALTER TABLE wp_posts
ADD KEY type_status_author (
post_type,
post_status,
post_author
);

Attachments (1)

2023-06-21 20_24_04-Просмотр медиа.png (94.2 KB) - added by Shoorf 20 months ago.

Download all attachments as: .zip

Change History (3)

#1 follow-up: @spacedmonkey
20 months ago

@Shoorf Thanks for your ticket. On a ticket like this, is highly recommended you using some sort of object cache like redis or memcache. Sites that large, will have performance and database issues without some kind of object caching.

#2 in reply to: ↑ 1 @Shoorf
20 months ago

Replying to spacedmonkey:

@Shoorf Thanks for your ticket. On a ticket like this, is highly recommended you using some sort of object cache like redis or memcache. Sites that large, will have performance and database issues without some kind of object caching.

Thanks for the answer! I'm surprised, but this system doesn't even require caching now: any of the admin or frontend pages are fully loaded in 400-600ms, and perhaps the only problem I've noticed so far is the one indicated in the ticket. The project has only a couple of plugins enabled, the display of tags in the post lists in the admin panel is disabled, and wp_postmeta is not used. The WordPress team has come a long way in 20 years, bravo!

Note: See TracTickets for help on using tickets.