[wp-trac] [WordPress Trac] #46179: Make fetch_array accessible in $wpdb

[WordPress Trac]

#46179: Make fetch_array accessible in $wpdb
-------------------------+-----------------------------
 Reporter:  alduinwf     |      Owner:  (none)
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Database     |    Version:
 Severity:  normal       |   Keywords:
  Focuses:  performance  |
-------------------------+-----------------------------
 Hi,

 I want to suggest a change in $wpdb. I'd like to see a possibility to use
 fetch_array by myself rather than taking the result of a query as an array
 or object.

 Let me explain why.

 WordPress doesn't scale very well when you handle large amounts of data
 from the database. For example, if you want to display a list of all posts
 and you have like 5000 of them. You'd use WP_Query for that. Which is
 fine, I guess, for a limited number, but slow in this use case.

 So you'd query yourself.


 {{{
 SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_type =
 'post' ORDER BY post_title ASC
 }}}


 If you give that to $wpdb, you'd receive an array with all 5600 posts and
 all the jazz attached to it. It takes 5 seconds and takes about 500 MB of
 PHP memory.

 I have now added an own class and established a second database
 connection. With that, I run the query above but the result remains in
 mysql. Instead of

 {{{#!php
 <?php
 foreach($results as $current) {
 // do stuff
 }
 }}}


 I now run
 {{{#!php
 <?php
 while($current = $mydb->fetch_array($results)) {
 // do stuff
 }
 }}}


 And I only need 30 MB of PHP RAM and 0.4s of time.

 This saves a lot of resources because you are able to get rid of a lot of
 PHP overhead. See, the MySQL result is in memory either way (because you
 did run the query) but you don't have to store everything from the result
 in PHP memory as well. You process them one at a time and only store (or
 output or whatever you need to do) what you actually need.

 I know this is nothing you'd usually do but there are other use-cases
 where an accessible fetch_array would come in handy in the interest of
 performance optimization - for example if your plugin utilizes a cronjob
 which has to handle large amounts of data.

 I don't assume this would break anything compatibility-wise as it is an
 additional method inside this class, so it would be a very nice addition.

 All you'd need is to add a new return method to $wpdb->query that returns
 the MySQL resource and a new method that exposes a fetch_array function to
 the public accepting said resource.

 Thank you a lot for reading my proposal and at least considering it :)

 -alduin

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/46179>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform