-
-
Save nawawi/89ba768bed66601882e124284d821bb7 to your computer and use it in GitHub Desktop.
How to remove the slow SQL_CALC_FOUND_ROWS from WP_User_Query and replace with standard COUNT() which is more performant.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?php | |
/** | |
* Before sql query is generated, intercept `query_vars` and set `count_total` to false | |
* to prevent `SQL_CALC_FOUND_ROWS` from being added to the sql query. | |
* Also set our new `query_vars` attribute to true to track if count is required. | |
*/ | |
add_action( 'pre_get_users', function($wpq) { | |
if ( isset($wpq->query_vars['count_total'] ) && $wpq->query_vars['count_total'] ) { | |
$wpq->query_vars['count_total'] = false; | |
$wpq->query_vars['run_count'] = true; | |
} | |
} ); | |
/** | |
* Here we have the `wp_user_query` object containing the generated sql query | |
* We hook to this action to run our own `count` on the table depending on the value | |
* of our custom `query_vars` attribute from above. | |
* We run the extra query on the db and store it in the class variable `total_users` | |
*/ | |
add_action( 'pre_user_query', function($wpq) { | |
global $wpdb; | |
if ( isset($wpq->query_vars['run_count']) && $wpq->query_vars['run_count'] ) { | |
unset($wpq->query_vars['run_count']); | |
$sql = "SELECT COUNT(*) $wpq->query_from $wpq->query_where"; | |
$wpq->total_users = $wpdb->get_var( $sql ); | |
} | |
} ); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment