I have two tables, ‘auth’ and ‘ips’, which I’d like to join in a query.
|column name||data type||Extra|
Context: I have a program running locally which monitors the /var/log/auth.log file and inserts ssh login attempts into this table. Contains about 3,200 rows and counting.
|column name||data type|
Index for start and end (in that order), the table is also ordered by start (ascending) and rows start and end only contain unique values.
|16777216||16777471||United States of America|
Context: this table stores IP ranges and the country to which it (very likely) is assigned to. IPs are stored as unsigned int as one would get from using INET_ATON. Contains 486,257 rows and is not added to anymore, only rarely updated in its entirety.
Now to the queries:
The query I’m currently using:
SELECT auth.date, auth.timestamp, auth.result, auth.ip, auth.user, ips.country FROM `auth` INNER JOIN ips ON (INET_ATON(auth.ip) >= ips.start AND INET_ATON(auth.ip) < ips.end) LIMIT 100;
This query takes approximately 9.7 seconds on my system, not even mentioning going through all 3200+ rows.
If I change the auth.ip references in the ‘ON’ clause to a static IP (let’s make one up: 18.104.22.168), we get the following query:
SELECT auth.date, auth.timestamp, auth.result, auth.ip, auth.user, ips.country FROM `auth` INNER JOIN ips ON (INET_ATON("22.214.171.124") >= ips.start AND INET_ATON("126.96.36.199") < ips.end) LIMIT 100;
This query returns in just 0.15 seconds.
- Why is the first query taking roughly 63 times longer than the second query?
- Can the first query be improved regarding performance?
If I missed something let me know, as it is my first post.