Efficiency on displaying random authors based on large database

2 comments

1. If you are concerned about efficiency, you might want to use the Transients API to store the query. Storing something that you want to randomize might seem counter-intuitive, but if you store the entire query, you can always randomize and manipulate the resulting array to get the results you want.

   Here’s how to get all the subscribers and store them in a transient, with a but of straight PHP at the end to shuffle/randomize the result and then pick off the first 5 results using array_slice()

   if ( false === ( $users = get_transient( 'get_all_subscribers' ) ) ) {
        // this code runs when there is no valid transient set
   
       $args  = array(
           'role'   => 'subscriber'
       );
   
       $wp_user_query = new WP_User_Query( $args );
   
       $users = $wp_user_query->get_results();
   
       set_transient( 'get_all_subscribers', $users );
   }
   
   // proceed with data normally
   // randomize the stored result
   shuffle( $users );
   $rand_users = array_slice( $users, 0, 5 );
   
   var_dump( $rand_users );
   

   and then to make sure the transient is up to date, we’ll delete it when a user is updated or added:

   // Create a simple function to delete our transient
   function delete_all_subscribers_transient() {
        delete_transient( 'get_all_subscribers' );
   }
   // Add the function to the profile_update and user_registration hooks
   add_action( 'profile_update', 'delete_all_subscribers_transient' );
   add_action( 'user_register', 'delete_all_subscribers_transient' );
   

2. You don’t want to fetch all users if you only need 5.

   If you want to use WP_User_Query() to fetch 5 users by random, you can try to use the pre_user_query hook to overwrite the orderby part:

   $args  = array(
       'role'   => 'subscriber',
       'number' => 5,
   );
   add_action( 'pre_user_query', 'my_pre_user_query' );
   $wp_user_query = new WP_User_Query( $args );
   

   where

   function my_pre_user_query( $q ){
       $q->query_orderby = ' ORDER BY RAND() ';
   
       // remove the hook
       remove_action( current_filter(), __FUNCTION__ );
   }
   

   Update:

   This article contains a trick to cut down the number of rows, using a special
   WHERE condition, before the ORDER BY RAND() kicks in … as far as I understand it.

   The WHERE condition in your case might be

   WHERE RAND()<(SELECT ((5/COUNT(*))*10) FROM wp_users)
   

   So if you have a very large number of users, then you could try out this modified action callback:

   function my_pre_user_query( $q ){
       $q->query_where = str_replace( 'WHERE 1=1', 'WHERE RAND()<(SELECT ((5/COUNT(*))*10) FROM wp_users) ', $q->query_where );
       $q->query_orderby = ' ORDER BY RAND() ';
   
       // remove hook
       remove_action( current_filter() , __FUNCTION__ );
   }
   

   if the trick from the above article is working!

   Here is a more general version of the modified callback:

   function my_pre_user_query( $q ){
   
       $limit = preg_replace( '/[^d]/', '', $q->query_limit );
   
       $from   = 'WHERE 1=1';
       $to     = sprintf( 'WHERE RAND()<(SELECT ((%d/COUNT(*))*10) FROM %susers)', 
                           $limit, 
                           $GLOBALS['wpdb']->prefix 
                );
   
       $q->query_where   = str_replace( $from, $to, $q->query_where );
       $q->query_orderby = ' ORDER BY RAND() ';
   
       // remove the hook    
       remove_action( current_filter() , __FUNCTION__ );
   }
   

Comments are closed.