Creating a numeric user ID from distinct combinations of IP and user agent from a server log
CREATE TABLE users (
    user_id INT UNSIGNED AUTO_INCREMENT NOT NULL,
    ip_address VARCHAR(39) CHARACTER SET ascii NOT NULL,
    user_agent VARCHAR(255) NOT NULL,
    PRIMARY KEY(id)
    UNIQUE(ip_address, user_agent)
) ENGINE=InnoDB;

INSERT INTO users (ip_address, user_agent)
    SELECT DISTINCT ip_address, user_agent
    FROM server_requests;


Notes:


39 is the max length for IPv6; don't say 255 (to avoid indexing problems).
The extra index solves the performance problem by having the PK needed to optimize LEFT JOIN users u  ON r.ip_adress=u.ip_adress  AND r.user_agent=u.user_agent