wc-smooth-generator
wc-smooth-generator copied to clipboard
[Enhancement]: Improve random user fetch
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.