UUID Field vs CRC Index Querying

uuid-crc-test.md

Performance Comparison: UUID Field vs CRC Index Querying

• MySQL version: 5.7.29

SELECT * FROM uuid_crc_test WHERE uuid = 'c3830c70-a8e6-11ef-b990-0242ac180005';

Number of records	Execution times (ms)
100 000	3.6, 2.3, 2.1, 1.9
1 000 000	3.3, 2.7, 2.1, 1.7

SELECT * FROM uuid_crc_test WHERE uuid_crc = 435844718;

Number of records	Execution times (ms)
100 000	2.3, 3.1, 1.9, 2.0
1 000 000	2.1, 2.4, 2.1, 1.8

Table creation

CREATE TABLE uuid_crc_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL,
    uuid_crc INT UNSIGNED NOT NULL,
    INDEX idx_uuid (uuid),
    INDEX idx_uuid_crc (uuid_crc)
) ENGINE=InnoDB;

MySQL UUID Generation with CRC

DELIMITER $$

CREATE PROCEDURE bulk_uuid_crc_insert()
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE current_uuid CHAR(36);
    
    -- Disable keys for faster insertion
    -- ALTER TABLE uuid_crc_test DISABLE KEYS;
    
    WHILE i < 100000 DO
        -- Generate UUID first
        SET current_uuid = UUID();
        
        -- Insert using the same UUID for both fields
        INSERT INTO uuid_crc_test (uuid, uuid_crc) 
        VALUES (current_uuid, CRC32(current_uuid));
        
        SET i = i + 1;
    END WHILE;
    
    -- Re-enable keys after insertion
    -- ALTER TABLE uuid_crc_test ENABLE KEYS;
END $$

DELIMITER ;

-- Execute the procedure
CALL bulk_uuid_crc_insert();