Jump to content

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


Go to solution Solved by requinix,

Recommended Posts

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?

Link to post
Share on other sites
  • Solution

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.
Link to post
Share on other sites

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?

Link to post
Share on other sites

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. 

Link to post
Share on other sites

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 by imgrooot
Link to post
Share on other sites

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.

Link to post
Share on other sites
$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.
Link to post
Share on other sites
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.

Link to post
Share on other sites
$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.

Link to post
Share on other sites

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.