Which db to select?

Anon

Hi,
I am having to do a application, where it will need to create random numeric ids(9 digit) in batches. And as to check for redundancy while generating ids.

For redundancy checks:
This happens in, FOR LOOP according to number of ids need to be created.

Once every new random 9 digit number is generated in loop, i will have to check in the mysql db if it exists or not. If not exists it will be added into array, if it exists then i will have to decrement so that the pin generation in loop repeats one more time.

Eg:

BatchNumber Number of pins

1001 5000
1003 10000
.
..
..
1010 1 lakh

like this ids created in all batches may come to millions of ids. Approximately 2-10 million ids in mysql db.

Will mysql be able to handle this???

How to optimize/tune the queries on php for faster results and better performance.

As per now to generate 7000 ids its taking 7 mins. Which is not very good!

Is it the problem of mysql or code?

If mysql cant handle this, which else db can do the job?

Heres the code have written.

<?
function genumber($length){

	    srand((double)microtime()*1000000);  

	    $nums = array("0", "1", "2", "3", "4","5","6","7","8","9"); 

	    $num = count($nums);  

	    for($i = 0; $i < $length; $i++){  
	        $number .= $nums[rand(0, $num - 1)] . $nums[rand(0, $num - 1)];  
	    }  

	    return substr($number, 0, $length);

<? for ($i = 1; $i <= $NoOfIds; $i++) {

		//random numberic generation - 9 digit
		$tmpin = genumber(9);

		$select_query_pin = "SELECT count(Id) FROM $db_table5 WHERE PinNo = '$tmpin'";
		$db_select_pin = mysql_query($select_query_pin,$db_conn) or mysql_error();
		$check_pin = mysql_fetch_row($db_select_pin);

		$select_query_pin1 = "SELECT count(Id) FROM $db_table8 WHERE PinNo = '$tmpin'";
		$db_select_pin1 = mysql_query($select_query_pin1,$db_conn_expired) or mysql_error();
		$check_pin1 = mysql_fetch_row($db_select_pin1);

		if (($check_pin[0] < 1) AND ($check_pin1[0] < 1 )) {
			$pin = $tmpin;
			$PinArr[$i] = $tmpin;
		}
		else {
			$i = $i--;
		}
	  }

$sql = "INSERT INTO $db_table5(a1,a2,a3,a4,a5,a6,a7,";

$sql.= "a8,a9,a10,a11,a12,a13,a14,a15,a16) VALUES ";

for ($j = 1; $j <= count($PinArr); $j++) {
$sql.= "($a1,'$a2,'$a3,'$a4,'$a5,'$a6,'$a7,$a8,";

$sql.= "'$a9,'$a10,'$a11,'$a12,0,'$a13,'$a14,'$a15)";

if ($j!=count($PinArr)) {
$sql.=',';
}

?>

Please let me know any info about this.

Thanks,
Raghu.