Skip to content

Instantly share code, notes, and snippets.

@bosunolanrewaju
Last active June 6, 2021 20:09
Show Gist options
  • Save bosunolanrewaju/fe7754adddc33faaa18f287289e92e6f to your computer and use it in GitHub Desktop.
Save bosunolanrewaju/fe7754adddc33faaa18f287289e92e6f 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.
<?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