Fastest way to generate 11,000,000 unique ids

Generating 11 Million Unique IDs: Strategies for PHP and MySQL

Explore efficient and robust methods for generating a large volume of unique identifiers (11,000,000+) using PHP and MySQL, focusing on performance and uniqueness guarantees.

Generating a large number of unique identifiers is a common requirement in many applications, from tracking user sessions to creating short URLs or product SKUs. When dealing with millions of IDs, performance, uniqueness, and scalability become critical concerns. This article delves into various strategies for generating 11,000,000 unique IDs using PHP and storing them efficiently in MySQL, highlighting the pros and cons of each approach.

Understanding Uniqueness and Performance Constraints

Before diving into implementation, it's crucial to define what 'unique' means in your context. Is it globally unique (like UUIDs) or unique within a specific table? For 11 million IDs, a simple auto-incrementing integer might suffice if uniqueness is only required within a single table and you're not concerned about predictability or distribution. However, if you need more robust, distributed, or less predictable IDs, other methods are necessary. Performance is also key; generating and inserting millions of records must be optimized to avoid long processing times and database bottlenecks.

flowchart TD
    A[Start ID Generation] --> B{Choose ID Strategy}
    B -->|Auto-Increment| C[Simple Integer IDs]
    B -->|UUID/GUID| D[Globally Unique IDs]
    B -->|Random String/Hash| E[Less Predictable IDs]
    C --> F{Batch Insert to MySQL}
    D --> F
    E --> F
    F --> G[Verify Uniqueness (if applicable)]
    G --> H[End ID Generation]

General workflow for generating and storing unique IDs.

Strategy 1: Auto-Increment with Batch Inserts

The simplest and often fastest way to generate a large number of unique IDs within a single MySQL table is to leverage its AUTO_INCREMENT feature. While PHP doesn't generate these IDs directly, it can trigger their creation by inserting rows. The key to performance here is using batch inserts, which significantly reduce the overhead of multiple individual INSERT statements. This method guarantees uniqueness within the table and is highly performant for sequential IDs.

<?php

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$num_ids_to_generate = 11000000;
$batch_size = 1000;

try {
    $pdo->beginTransaction();
    for ($i = 0; $i < $num_ids_to_generate; $i += $batch_size) {
        $values = [];
        for ($j = 0; $j < $batch_size; $j++) {
            $values[] = '(NULL)'; // Or any other data if needed
        }
        $sql = 'INSERT INTO unique_ids (id) VALUES ' . implode(',', $values);
        $pdo->exec($sql);
    }
    $pdo->commit();
    echo "Successfully generated {$num_ids_to_generate} auto-increment IDs.\n";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
}

?>

PHP script for generating 11 million auto-increment IDs using batch inserts.

Strategy 2: UUIDs (Universally Unique Identifiers)

UUIDs (or GUIDs) are 128-bit numbers used to uniquely identify information in computer systems. They are designed to be unique across all space and time, making them excellent for distributed systems where database auto-increment isn't feasible or desirable. PHP can generate UUIDs, and they can be stored in MySQL as CHAR(36) or BINARY(16) (for space efficiency). While generation is fast, indexing and storage can be less efficient than integers, especially if not stored as BINARY(16).

<?php

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$num_ids_to_generate = 11000000;
$batch_size = 1000;

function generateUuidV4() {
    // Generate 16 bytes (128 bits) of random data
    $data = random_bytes(16);

    // Set version to 0100 (4) and variant to 10xx (RFC 4122)
    $data[6] = chr(ord($data[6]) & 0x0f | 0x40); // set version to 0100
    $data[8] = chr(ord($data[8]) & 0x3f | 0x80); // set variant to 10xx

    // Format the UUID
    return vsprintf('%s%s-%s-%s-%s-%s%s%s',
        str_split(bin2hex($data), 4));
}

try {
    $pdo->beginTransaction();
    for ($i = 0; $i < $num_ids_to_generate; $i += $batch_size) {
        $values = [];
        for ($j = 0; $j < $batch_size; $j++) {
            $uuid = generateUuidV4();
            $values[] = $pdo->quote($uuid);
        }
        $sql = 'INSERT INTO unique_uuids (uuid_col) VALUES (' . implode('), (', $values) . ')';
        $pdo->exec($sql);
    }
    $pdo->commit();
    echo "Successfully generated {$num_ids_to_generate} UUIDs.\n";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
}

?>

PHP script for generating 11 million UUIDs (version 4) and inserting them in batches.

Strategy 3: Custom Random String Generation with Collision Checks

For cases where UUIDs are too long or auto-increment is too predictable, a custom random string generator can be used. This involves generating a string of a certain length and character set, then checking for uniqueness before insertion. For 11 million IDs, the probability of collision increases significantly, making a robust collision detection and retry mechanism essential. This method is generally slower due to the need for uniqueness checks, but offers flexibility in ID format.

<?php

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$num_ids_to_generate = 11000000;
$id_length = 10; // e.g., 10 characters long
$characters = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
$char_count = strlen($characters);
$batch_size = 500; // Smaller batch size due to potential retries

function generateRandomString($length, $characters, $char_count) {
    $randomString = '';
    for ($i = 0; $i < $length; $i++) {
        $randomString .= $characters[random_int(0, $char_count - 1)];
    }
    return $randomString;
}

try {
    $pdo->beginTransaction();
    $generated_count = 0;
    while ($generated_count < $num_ids_to_generate) {
        $values = [];
        $batch_ids = [];
        for ($j = 0; $j < $batch_size; $j++) {
            $id = generateRandomString($id_length, $characters, $char_count);
            $batch_ids[] = $pdo->quote($id);
        }

        // Check for existing IDs in the current batch and database
        $check_sql = 'SELECT custom_id FROM unique_custom_ids WHERE custom_id IN (' . implode(',', $batch_ids) . ')';
        $existing_ids_stmt = $pdo->query($check_sql);
        $existing_ids = $existing_ids_stmt->fetchAll(PDO::FETCH_COLUMN);
        $existing_ids_map = array_flip($existing_ids);

        foreach ($batch_ids as $quoted_id) {
            $id = trim($quoted_id, "'"); // Remove quotes for map lookup
            if (!isset($existing_ids_map[$id])) {
                $values[] = '(' . $quoted_id . ')';
                $generated_count++;
                if ($generated_count >= $num_ids_to_generate) break;
            }
        }

        if (!empty($values)) {
            $insert_sql = 'INSERT IGNORE INTO unique_custom_ids (custom_id) VALUES ' . implode(',', $values);
            $pdo->exec($insert_sql);
        }
    }
    $pdo->commit();
    echo "Successfully generated {$generated_count} custom unique IDs.\n";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
}

?>

PHP script for generating custom random strings with collision detection and batch insertion.

Database Schema Considerations

Regardless of the generation method, your MySQL table schema plays a vital role in performance. For AUTO_INCREMENT IDs, a simple INT or BIGINT primary key is ideal. For UUIDs, BINARY(16) is highly recommended over CHAR(36) for storage and indexing efficiency. For custom random strings, use an appropriate VARCHAR length with a UNIQUE index. Always ensure your tables use an efficient storage engine like InnoDB.

erDiagram
    UNIQUE_IDS {
        BIGINT id PK "Auto-incremented ID"
    }
    UNIQUE_UUIDS {
        BINARY(16) uuid_col PK "UUID stored as binary"
    }
    UNIQUE_CUSTOM_IDS {
        VARCHAR(20) custom_id PK "Custom random string"
    }

Example database schemas for different ID types.