Is there any downsides to using this method to randomly select a user id from a table?

[imgrooot]

SELECT user_id FROM users WHERE active = :active ORDER BY RAND() LIMIT 1

The above query works. But what if the users table is filled with thousands of users? Will this query break or be very slow? If so, what's the alternative solution to this?

[benanamen]

A better question is why do you want to do this?

[requinix]

Yes, it will be slow.

 

If you only want one then do

SELECT COUNT(1) FROM users WHERE active = :active

SELECT user_id FROM users WHERE active = :active LIMIT x, 1

where "x" is the value from the first query.

[imgrooot]

Yes, it will be slow.

 

If you only want one then do

SELECT COUNT(1) FROM users WHERE active = :active

SELECT user_id FROM users WHERE active = :active LIMIT x, 1

where "x" is the value from the first query.

 

 

Yes I only need to pick 1 random user from the whole users table.  Based on your queries, is it really picking a random user?

[imgrooot]

A better question is why do you want to do this?

 

It's one of the functions I require. For eg. if a user signs up on the website, that new user will be attached to a random member who is already in the database. 

[requinix]

Ah, shoot, I forgot a step:

$x = mt_rand(0, $count - 1);

Or

FLOOR(RAND() * COUNT(1)) AS x

Edited April 11, 2017 by requinix

[benanamen]

Okay, again I ask, why do you want to do that as far as attaching to a random number in your database? And just where does this random number come from?

[imgrooot]

Ah, shoot, I forgot a step:

$x = mt_rand(0, $count - 1);

Or

FLOOR(RAND() * COUNT(1)) AS x

 

Alright so based on what you said, here are the queries.  It seems to work. But I've noticed this selects the the user with lowest user_id and checks if all it's positions are filled. If they are, then it goes to the next user with the higher user_id and checks the same thing.  Is this suppose to do that? If so, this might be even better solution than before.

$count = $db->prepare("SELECT COUNT(1) FROM users WHERE filled_positions < :filled_positions");
$x = mt_rand(0, $count - 1);

$find_random_user = $db->prepare("SELECT user_id FROM users WHERE filled_positions < :filled_positions LIMIT :x, 1");
$find_random_user->bindParam(':x', $x);

Edited April 11, 2017 by imgrooot

[imgrooot]

Okay, again I ask, why do you want to do that as far as attaching to a random number in your database? And just where does this random number come from?

 

I think you misunderstood. I meant a "MEMBER", not a number. A member that is already in the users table. This member will serve as a sponsor to the new user who is signing up.

[requinix]

$count = $db->prepare("SELECT COUNT(1) FROM users WHERE filled_positions 
$x = mt_rand(0, $count - 1);

Looks like you completely forgot the part about executing the query.

[NigelRel3]

SELECT user_id FROM users WHERE active = :active ORDER BY RAND() LIMIT 1

The above query works. But what if the users table is filled with thousands of users? Will this query break or be very slow? If so, what's the alternative solution to this?

 

One thing to think about is that although this query may not be the most optimal, it's also not the most frequently used - unless you have thousands of people joining at a time.  If you had millions of users then it may be a slight issue.

So it's not such a big deal to do it this way, although it's still interesting to see other solutions.

[imgrooot]

$count = $db->prepare("SELECT COUNT(1) FROM users WHERE filled_positions < :filled_positions");
$x = mt_rand(0, $count - 1);

Looks like you completely forgot the part about executing the query.

 

 

Oops. You're right of course. Completely went over my head. Thanks for pointing that out.

[imgrooot]

One thing to think about is that although this query may not be the most optimal, it's also not the most frequently used - unless you have thousands of people joining at a time.  If you had millions of users then it may be a slight issue.

So it's not such a big deal to do it this way, although it's still interesting to see other solutions.

 

Good to know. That's what I wanted to know.

[benanamen]

My mistake imgrooot, I shouldn't be responding to posts when I am extremely tired. 

[imgrooot]

My mistake imgrooot, I shouldn't be responding to posts when I am extremely tired. 

 

No worries. Member and Number can be easily mistaken when tired.