Randomly Generate 4 digit Code & Check if exist, Then re-generate

My code randomly generates a 4 or 5 digit code along with 3 digit pre-defined text and checks in database, If it is already exists, then it regenerates the code and saves into database.

But sometimes the queries get stuck & become slower, if each pre-defined keyword has around 1000 record.

Lets take an example for one Keyword "XYZ" and Deal ID = 100 and lets say it has 8000 records in database. The do while loops take a lot of time.

$keyword = "XYZ"; // It is unique for each deal id.
dealID = 100; // It is Foreign key of another table.
$initialLimit = 1;
$maxLimit = 9999;

do {
    $randomNo = rand($initialLimit, $maxLimit);
    $coupon = $keyword . str_pad($randomNo, 4, '0', STR_PAD_LEFT);

    $findRecord = DB::table('codes')
        ->where('code', $coupon)
        ->where('deal_id', $dealID)
        ->exists();

    } while ($findRecord == 1);

As soon as the do-while loops end, Record is being inserted into database after above code. But the Above code takes too much time,

The above query is printed as follow in MySQL. like for above example deal id, it has already over 8000 records. The above code keeps querying until it finds. When traffic is high, app becomes slower.

select exists(select * from `codes` where `code` = 'XYZ1952' and `deal_id` = '100');
select exists(select * from `codes` where `code` = 'XYZ2562' and `deal_id` = '100');
select exists(select * from `codes` where `code` = 'XYZ7159' and `deal_id` = '100');

Multiple queries like this get stuck in database. The codes table has around 500,000 records against multiple deal ids. But Each deal id has around less than 10,000 records, only few has more than 10,000.

Any suggestions, How can I improve the above code?

Or I should use the MAX() function and find the code and do the +1 and insert into db?

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/30PlVzZ
via IFTTT