[wp-trac] [WordPress Trac] #11622: switch to PDO::prepare when we require PHP 5.1

[WordPress Trac]

#11622: switch to PDO::prepare when we require PHP 5.1
-------------------------+--------------------------------------------------
 Reporter:  sirzooro     |       Owner:  ryan                    
     Type:  enhancement  |      Status:  new                     
 Priority:  normal       |   Milestone:  Future Release          
Component:  Database     |     Version:  2.9                     
 Severity:  normal       |    Keywords:  needs-patch dev-feedback
-------------------------+--------------------------------------------------

Comment(by Denis-de-Bernardy):

 Replying to [comment:10 hakre]:
 >  ''A true prepared statement would be very bad, because it would do a
 return trip to the server for every query, and the server would actually
 prepare the statement ...'' ([/ticket/11608#comment:24 Ref. to Denis-de-
 Bernardy])

 Yeah, I'd like to stress this point, because it's a bit subtle. A prepared
 statement will tell MySQL to come up with a query plan that fits for a
 given set of variables.

 For instance, if you ask MySQL to prepare the following:

 {{{
 SELECT * FROM $wpdb->posts WHERE post_status = ? AND post_type = ? ORDER
 BY post_date LIMIT ?
 }}}

 The query planner would likely end up with a plan that fetches all rows
 and sorts the mess in memory (or worse, on the hard drive): the limit is
 an unknown, and it has no idea of how many rows might be returned due to
 the constraints on the type and status being unknowns as well.

 Fill in the variables, however, and you get this:

 {{{
 SELECT * FROM $wpdb->posts WHERE post_status = 'publish' AND post_type =
 'post' ORDER BY post_date LIMIT 10
 }}}

 In this case, the query planner can now take advantage of the table's
 stats, and knows that using an index on (post_status, post_type,
 post_date), for instance, will yield a very good plan.

 When you use PDO::ATTR_EMULATE_PREPARES, the query no longer is prepared
 by MySQL. Rather, PDO sticks to using the table's row definitions and
 works out how to escape what based on them. The final query, without
 variables, is then sent to the query planner, which leads us to the second
 use-case above.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/11622#comment:11>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software