The efficiency problem of inserting a unique random string into Mysql by PHP

  1. there is a CAPTCHA data table with 2 million pieces of data. CAPTCHA fields need to be unique
  2. you need to insert a large amount of data into this table, about tens of thousands of pieces of data at a time
  3. how to optimize the execution time of each time

current practice:

    Mr.
  1. makes an array containing the unique CAPTCHA.
  2. check the verification code table according to the array, and find the existing regenerations
  3. 5000 insert in batches at a time
private function generateUniqueValidationCodes(int $number)
{
    // 
    $codeArr = $this->generateUniqueCodes($number);
    // 
    $result = ValidationCode::query()->whereIn("validation_code", $codeArr)->get();
    // 
    if ($result->isNotEmpty()) {
        $this->generateUniqueValidationCodes($number);
    } else {
        return $codeArr;
    }
}

there is a minor error in the code

$this->generateUniqueValidationCodes($number);

here we have to add that the return
CAPTCHA field requires unique , so why is this unique?
if it's the only thing, you can add Unique Index

.

agrees with the upstairs view that it is not necessary to query whether there is any repetition before each insertion, just add a unique index, as long as it is executed as many times as you want;
then the successful execution is not repeated, and it can be called again if it fails. This saves the time needed for each query, and the efficiency will be improved a lot


there are return in the code, not finished, and there is also a unique index in the data table, which is the only business need. What I would like to ask is is there any other optimization method?


first of all, since it needs to be unique, it can be solved by adding a unique index, and then recursively executed if it fails. And since the CAPTCHA is defined by you, then the generation rules are also in your hands. You should distinguish from the CAPTCHA generation rules, which basically ensures uniqueness, there is no need to consider too much, and simple problems can be dealt with simply.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1b31a01-34176.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1b31a01-34176.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?