mysql – Why does this sql join query take much longer using a reference to a row from the first table in the ‘on’ clause versus using a literal variable?

I have two tables, ‘auth’ and ‘ips’, which I’d like to join in a query.

Table ‘auth’:

column name data type Extra
id int AI
date varchar(6)
timestamp varchar(8)
result varchar(8)
ip varchar(15)
user text
service varchar(4)

No indexes.
Example row:

1 Aug 29 03:39:31 Failed 39.109.71.83 etherpad ssh2

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.

Table ‘ips’:

column name data type
start int (unsigned)
end int (unsigned)
country text

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.
Example row:

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: 200.200.200.200), 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("200.200.200.200") >= ips.start AND INET_ATON("200.200.200.200") < ips.end) LIMIT 100;
This query returns in just 0.15 seconds.

  1. Why is the first query taking roughly 63 times longer than the second query?
  2. Can the first query be improved regarding performance?

If I missed something let me know, as it is my first post.