Database Performance

[ctb]

Hi everyone,

I need your expert advice on how to solve this issue. I created a web based tool where it generates random username and will store in the database (MYSQL). The user will specify the quantity of how many username to be generated.

My issue is when the user demanded for more than 500 usernames, the database slows down and sometime halted without completing the transaction.

my code:

$query= "insert into profile (username) values";
for($i=0; $i<=$quantity; $i++) {
$username = generateRandomID();
$query.= "($username),";
}
$query = substr($query, 0, -1);

other suggestion/ways to make the generation be more efficient?

i also considering it might be a connection issue from the server.. but before i blame the server, i want ur ideas.

thanks very much in advance.

ctb

[drawmack]

You're query is getting bloated, try this

<?php
$query= "insert into profile (username) values";
for($i=0; $i<=$quantity; $i++) {
    if(($i+1)%500 == 0) {
        mysql_query(substr($query, 0, -1));
        $query= "insert into profile (username) values";
    } //end if
    $username = generateRandomID();
    $query.= "(\"$username\"),";
}
$query = substr($query, 0, -1);  

?>