Looping through 50000+ records?? any efficient way?

[asp_funda]

I thought the problem was that he was hitting the DB multiple times as he was getting used ID's coming up,

so it'll be when I use that incremental ID stuff now, like I said, the IDs are dispersed among a lot of series, so if the incremental approach is taken, the DB will be hit multiple times even then, since there'll be quite a lot of non-unique IDs that'll be generated in that approach as well.

[Mandes]

.....like I said, the IDs are dispersed among a lot of series, so if the incremental approach is taken, the DB will be hit multiple times even then......

Perhaps I'm missing something here, bear with me if I'm being thick.

Your structure allows for 4,569,760,000 combinations, you've used 60,000.

Youre saying that the existing ID's are fairly evenly dispersed across the entire range, now I know they're not going to be perfectly spaced, and maybe one or two will be next to each other. But, over the entire range between every used ID you have theroretically, on average, 76,162 ID's not used.

Even if we assume that this good luck will only be true for 10% of new ID's required thats still over 7000 ID's you can issue by only hitting the DB once, OK the 7001st will require 2 hits to jump over the Used ID.

What am I missing ?

[Snortles]

Perhaps I'm missing something here, bear with me if I'm being thick.

Your structure allows for 4,569,760,000 combinations, you've used 60,000.

Youre saying that the existing ID's are fairly evenly dispersed across the entire range, now I know they're not going to be perfectly spaced, and maybe one or two will be next to each other. But, over the entire range between every used ID you have theroretically, on average, 76,162 ID's not used.

Even if we assume that this good luck will only be true for 10% of new ID's required thats still over 7000 ID's you can issue by only hitting the DB once, OK the 7001st will require 2 hits to jump over the Used ID.

What am I missing ?

I don't understand it either at this point.

[asp_funda]

Perhaps I'm missing something here, bear with me if I'm being thick.
.....
What am I missing ?

What you said is correct, but to understand what I said which you didn't get, refer to what I said when this solution was proposed, I said its slightly better that what I'm using, that's all. Like you yourself said, it'll hit a snag, its only a short term fix, & we can't say for sure that it'll hit a duplicate after 7000 IDs, it can hit duplicates many times just after 500-600 IDs & then it may not hit one for the next 10000-15000 IDs. We can only know this for exact if we go through every ID & put them in their series, so that we know which series have the least number of occupants, which will be a bit time consuming & I can't do that. Nevertheless, I didn't rule out this solution either, I said I'll keep it, but not as the one to be deployed, maybe as an alternate one.

So far, I liked the idea of having the un-occupied unique IDs in another table & then taking them out one per signup, this can be done, it'll require me to do the hardwork, but will eliminate the user frustration of slow registration process!!

So if anyone has any other fix then let me know else I think I'll proceed with that one.

[mwolfe]

I honestly can't figure out why this script takes a long time to execute to begin with.. I am testing out something similar (but simplified a bit i'm sure). And i'm getting results that are extremely fast and i'm not even running a that fast of a computer.. What i did was i used the script that i posted on here a few days ago, i ran that and put several hundres thousand entries in my db.. Now i'm testing how long it takes to insert a new unique id into this table. My results are typically 0.0012 seconds. The worst i've had was like 0.0025 seconds. I know your page is going to have a lot of other things to slow it down as well, but this obviously isnt one of the things that should be slowing it down. Maybe the problem lies somewhere else....

here is the script i wrote so you can test it

<?php
function getRandomAlphaNumID() {
    $randomAlphabet = null;
    $randomNumber = null;
    for($i=1; $i<=4; $i++) {
        $randomNumber .= rand(0,9);
        $randomAlphabet .= chr(rand(65,90));
    }
    return $randomAlphabet.$randomNumber;
} 
function start_time() {
    $starttime = microtime(); 
    $startarray = explode(" ", $starttime); 
    return $startarray[1] + $startarray[0];
}

function end_time($starttime) {
    $endtime = microtime(); 
    $endarray = explode(" ", $endtime); 
    $endtime = $endarray[1] + $endarray[0]; 
    return round($endtime - $starttime, 5);
}

?>
<html>
<head><title>Random Test</title>
<body>

<?php
if (isset($_POST['submit'])) {
    $starttime= start_time();
    $con = mysql_connect("localhost", "myusername", "mypasswd");
    mysql_select_db("random");
    do {
        $num = getRandomAlphaNumID();
        $check = mysql_query("SELECT COUNT(*) FROM test WHERE random_id='$num'");
    } while (mysql_result($check, 0,0)>0);
    $insert = mysql_query("INSERT INTO test (random_id) values('$num')");
    if (!$insert) {
        echo mysql_error();
    }
    $endtime = end_time($starttime);
    echo "that took $endtime seconds to execute <br />";
}
?>
<p>Press enter to generate a new random id and test how long it takes<p>
<form method="POST" action="<?php echo $_SERVER['PHP_SELF'];?>">
<input type="submit" name="submit" value="generate"/>
</form>
</body>
</html>

[asp_funda]

I know your page is going to have a lot of other things to slow it down as well, but this obviously isnt one of the things that should be slowing it down. Maybe the problem lies somewhere else....

I doubt anything else is responsible, as the signup page only generates the ID & then puts the user in a secondary table. The users are supposed to be in a tree as per their referrer & vacant slots below them, this was supposedly taking time earlier so I had this stuff moved to a cron that takes the users from the secondary table & places them one by one in the main table, so the signup script just generates the ID & places the user in the table, nothing else.

I've decided on the suggestion put forward by you, of having the dump of un-used IDs & assigning them one by one. This will immensely speed up the signup process as well. For the time being, the signup process is running like it was before, but using mt_rand() instead of rand() till I get about 50000 or so IDs generated & ready for use!!

Thanks everyone, you've been a great help, all of you!!

[Mandes]

mwolfe

I looped your code and entered 345,448 ID's in my database (col set to unique). I only had 5 duplicate ID's that had to try a second time, this process took 7 mins.

Then I used your code as supplied to enter another 200 ID's, average entry time 0.0015 secs, longest time taken 0.25 secs.

Edit:


Oh yea, forgot to say, I didn't index the table either

[Etnu]

I think you're worrying just a tad too much. Your data range is large enough that the chances of a dupe hit are about 1 in 5000 for entries between 500,000k & 1,000,000 users. You won't even get to the point of 1 / 1000 hit ratios until you've filled up several million IDs. 99.9% of the time you'll get a valid id on the first try, and the other 0.1% of the time you'll have to do a second query (which has a 99.9% success rate as well). You really have no problem -- at all -- until you've got about a billion entries, in which case you'll be hitting 1/4. I'll bet a million dollars that you never get a billion legitimate users signed up for your site.

[asp_funda]

Yeah, maybe you are right, I've put in time-logging for every signup to see how much time the script takes to signup a user & to generate an ID as well, then I'll know for sure whether I should go ahead with the ID repository solution or not.

I'll bet a million dollars that you never get a billion legitimate users signed up for your site.

a billion is just too far, we don't expect any more than 20% of a million.