wc-smooth-generator icon indicating copy to clipboard operation
wc-smooth-generator copied to clipboard

[Enhancement]: Improve random user fetch

Open ovidiul opened this issue 3 years ago • 0 comments

Describe the solution you'd like

On a large site test with >1M users, this line related to orders generation seems to trigger a slow query as its looking through all the rows as:

> explain select * from wp_users ORDER BY rand() LIMIT 1;
+------+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+------+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+
|    1 | SIMPLE      | wp_users | ALL  | NULL          | NULL | NULL    | NULL | 1224953 | Using temporary; Using filesort |
+------+-------------+----------+------+---------------+------+---------+------+---------+---------------------------------+
1 row in set (0.000 sec)

Describe alternatives you've considered

As a potential solution, this code could be used to get the same random user in a less expensive way

$total_users = (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->users}" );
$offset          = wp_rand(0, $total_users);
			
$user_id = (int) $wpdb->get_var( "SELECT ID FROM {$wpdb->users} LIMIT $offset, 1" ); // phpcs:ignore
return new \WC_Customer( $user_id );

The select count(*) is less expensive as its using the index to count the items, then using the LIMIT offset, 1 to get the actual random result.

> explain select count(*) from wp_users;
+------+-------------+----------+-------+---------------+---------------+---------+------+---------+-------------+
| id   | select_type | table    | type  | possible_keys | key           | key_len | ref  | rows    | Extra       |
+------+-------------+----------+-------+---------------+---------------+---------+------+---------+-------------+
|    1 | SIMPLE      | wp_users | index | NULL          | user_nicename | 202     | NULL | 1224953 | Using index

Additional context

Happy to submit a PR if this can be considered.

ovidiul avatar Jan 27 '22 13:01 ovidiul