Fast and memory-optimized checking for values in database using php

Question

I have a function to generate n random string with the same length, making sure there isn't any duplicates, and then saves them to the database. Right now, to speed things up, first I get all the saved string from the database, convert them to a dictionary, and then check against them for duplicate every time it generate a random string rather than checking from database every time.

This is my code:

// get all codes first
$savedCodes = $this->Product_model->getAllCodes();
$dictionary = array();
for ($i = 0; $i < count($savedCodes); $i++)
    $dictionary[$savedCodes[$i]['code']] = 1;

$input_code = intval($this->input->post('qty'));
$query = "insert into products (code, trialCount, date_created, status) values";
for ($i = 0; $i < $input_code; $i++)
{
    do
    {
        $data['code'] = substr(str_shuffle($chars), 0, 3) . substr(str_shuffle($numbers), 0, 15);
        $exist = isset($dictionary[$data['code']]);
        // $exist = $this->Product_model->get(array('code' => $data['code']));
    }
    while ($exist == true);

    if ($i > 0)
    {
        if ($i % 900 == 0)
        {
            $query .= ';';
            $this->Product_model->insertBulk($query);
            $query = 'insert into products (code, trialCount, date_created, status) values';
        }
        else
            $query .= ',';
    }
    $query .= " ('" . $data['code'] . "', 0, " . $data['date_created'] . ", 'active')";

    $dictionary[$data['code']] = 1;
    $success = $success+1;
}

$this->Product_model->insertBulk($query);

Notice that I use isset for checking to speed things up.

Now, the problem is as the table size increase, the size of $dictionary and $savedCodes also increases. So, I encounter messages like Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 14 bytes) on line $dictionary[$savedCodes[$i]['code']] = 1;

Is there anyway to solve this? I don't want to use $exist = $this->Product_model->get(array('code' => $data['code'])); as it will make the function very very slow.

EDIT: I use public hosting service, so I cannot increase the php max_memory value

Answer 1

What you could do is create a new table UnusedCode with fields UnusedCodeId and code and populate it with as many random (or not) strings as you like -- do that using another program.

In your production app, just select the first record from UnusedCode, use the code, and delete the record from the table.