Database query needs optimization - function post_exists

[arpit.tambi.in]

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.

[nacin]

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.

[c3mdigital]

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.