[wp-trac] [WordPress Trac] #12257: wpdb Scales Badly Due to Unnecessary Copies of All Query Results

[WordPress Trac]

#12257: wpdb Scales Badly Due to Unnecessary Copies of All Query Results
--------------------------+-------------------------------------------------
 Reporter:  miqrogroove   |       Owner:  ryan                  
     Type:  defect (bug)  |      Status:  new                   
 Priority:  normal        |   Milestone:  Future Release        
Component:  Database      |     Version:                        
 Severity:  critical      |    Keywords:  dev-feedback has-patch
--------------------------+-------------------------------------------------
Changes (by joelhardi):

 * cc: joel@… (added)
  * keywords:  dev-feedback => dev-feedback has-patch


Comment:

 I just added a patch to wpdb (trunk r15628) that does away with the
 {{{$last_result}}} object property and instead uses a new property
 {{{$mysql_last_result}}} to store the MySQL resource of the current query.
 I believe this is what miqrogroove was after. Since I changed the type
 being stored (from {{{array}}} to MySQL {{{resource}}}), I chose not to
 repurpose the existing var.

 I've never liked wpdb for SELECTs, since IMHO it just recreates the
 standard PHP mysql client library interfaces (i.e. not much abstraction)
 with less flexibility and efficiency. So, I'm happy to contribute a patch
 that helps it do less copying.

 And, I contemplated doing an Iterator or at least separate table model
 class for result sets, but once I got into the code, the fact that the
 existing wpdb data access methods are all basically thinly abstracted
 versions of mysql_fetch_row, mysql_fetch_assoc and mysql_fetch_object made
 it very easy to simply rewrite these methods to use the old mysql client
 layer.

 I haven't done any serious testing or benchmarking, but running through ab
 using different parameters showed a 3% improvement on front page
 generation on a very small data set (memory_get_peak_usage of 11M). I
 would expect a larger improvement on bigger recordsets. Plus, fixing the
 WSOD on memory-bound queries.

 Obviously wpdb is a critical part of core, but IMHO this is a pretty
 conservative/safe changeset and worth testing.

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