Creating a numeric user ID from distinct combinations of IP and user agent from a server log I'm trying to create a numeric user ID from the distinct combination of IP addresses (alphanumerically anonymized) and the user agents. The table is a pre-processed server log file and contains among other variables the url, the IP address and the user agent of every html-request (click) the users produce.

First I created a table of distinct combinations of IPs and user agents. Among the 15 million requests, there are 1.5 million distinct combinations. I added an auto-incrementing ID:

CREATE TABLE users (ip_adress varchar(255), user_agent varchar(255));
INSERT INTO users (ip_adress, user_agent)
SELECT ip_adress, user_agent
FROM (SELECT DISTINCT ip_adress, user_agent FROM server_requests) AS internalQuery
order by ip_adress asc;
ALTER TABLE users ADD user_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;


Now I need to add the user_id from the table users to the table server_requests. What do you think of this query? There is no error, but the query takes far too long (over 24h, now i stopped it):

SELECT
r.request_id,
r.request_datetime,
u.user_id,
r.url_string,
r.ip_adress,
r.user_agent
FROM server_requests r
LEFT JOIN users u
ON r.ip_adress=u.ip_adress
AND r.user_agent=u.user_agent
ORDER BY r.request_id ASC;


Do you have any idea how to improve the query? Or is it normal, that it takes so much time? (Query example tested with MySQL)

I did this query in MySQL for testing reasons. But I plan to use PostgreSQL for the final query. I'm sure there is an equivalent to AUTO_INCREMENT in PostgreSQL. Because the IP addresses are alphanumerically anonymized, I used VARCHAR. The length of (255) is definitely overkill in this case.
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