Jump to content

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


imgrooot

Recommended Posts

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 comment
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);
Link to comment
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 comment
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 comment
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 comment
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 comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • 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.