Make fetch_array accessible in $wpdb

[alduinwf]

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
foreach($results as $current) {
// do stuff
}

I now run

<?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