[wp-trac] [WordPress Trac] #21051: Database query needs optimization - function post_exists

[WordPress Trac]

#21051: Database query needs optimization - function post_exists
----------------------------+-----------------------------
 Reporter:  arpit.tambi.in  |      Owner:
     Type:  enhancement     |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  Database        |    Version:  3.4
 Severity:  normal          |   Keywords:
----------------------------+-----------------------------
 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 at 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.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/21051>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software