WordPress.org

Make WordPress Core

Opened 2 years ago

Closed 11 months ago

Last modified 11 months ago

#21051 closed enhancement (wontfix)

Database query needs optimization - function post_exists

Reported by: arpit.tambi.in Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.4
Component: Performance Keywords:
Focuses: Cc:

Description

Function post_exists in /wp-admin/includes/post.php is causing full table scans and sometimes it takes upto 30 seconds when there are millions of records in wp_posts table.

Sample query -

# Time: 120623  2:16:28
# User@Host: test[test] @ localhost []
# Query_time: 17  Lock_time: 0  Rows_sent: 0  Rows_examined: 13952234
SELECT ID FROM wp_posts WHERE 1=1 AND post_title = 'MIO WALKBLK'AND post_content = 'Mio Walk Black MENS HEART RATE DIGITAL FACE<br /><br /> Brand: MIO<br /> Style: HRM<br /> Size: Mens<br /> Attachement: Black Resin Band';

On running EXPLAIN output generated is

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	wp_posts 	ALL 	NULL	NULL	NULL	NULL	13952445 	Using where

Clearly its not making use of any index on wp_posts and thus causes full table scans and slow queries.

Suggestions:
More parameters like 'post_type', 'post_author', and 'post_category' can be added to improve functionality of post_exists function and this might also improve performance of database queries.

Change History (4)

comment:1 scribu2 years ago

  • Component changed from Database to Performance

comment:2 nacin2 years ago

post_exists() isn't used anywhere in core, but importers make use of it to identify if a piece of content that is trying to be imported, was already imported previously.

In general, it should surely not be used. If it is a problem, perhaps we can deprecate it and copy its logic into a method of the WP_Importer class. Certain importers like the WXR importer could make use of GUIDs. Others, not as much.

comment:3 c3mdigital11 months ago

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

I agree that it would be nice to have a better way of verifying if a post exists but optimizing this function that does looks for verbatim post_content is not the way to do it. Another idea out there is to index the GUID column #18315.

comment:4 SergeyBiryukov11 months ago

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