Subquery en indexering voor IP-informatie

Ik ondervind een probleem bij het zoeken naar kruisverwijzingen tussen een tabel met gebruikersinformatie en een tabel met geolocatiegegevens (uit de database van GeoIP).

Ik heb het IP-adres in standaardformaat (geen geheel getal) in de gebruikerstabel en de GeoIP-gegevens in de GeoIP-tabel, met de integer IP-bereiken.

Deze query werkt, maar is erg traag en niet-geoptimaliseerd.

SELECT email, country 
FROM users 
INNER JOIN geoip ON users.ip BETWEEN geoip.startip AND geoip.endip

Ik heb het gevoel dat ik hier iets heel gemakkelijk mis.

UPDATE: deze query werkt, maar is erg langzaam - is er een manier om het te indexeren zodat het sneller gaat? Op dit moment, ongeacht de run, zou elke rij ongeveer 300-500ms moeten duren om uit te voeren, wat veel te langzaam is.

SELECT email, country 
FROM users INNER JOIN geoip ON INET_ATON(users.ip) 
BETWEEN geoip.startip AND geoip.endip

Bedankt!

UPDATE 2: Hier is de EXPLAIN-uitvoer van de query:

+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | geoip     | ALL  | NULL          | NULL | NULL    | NULL |  3651972 |             | 
|  1 | SIMPLE      | users     | ALL  | NULL          | NULL | NULL    | NULL | 87996123 | Using where | 
+----+-------------+-----------+------+---------------+------+---------+------+----------+-------------+

Ik kan momenteel geen rij met alleen integer-only IP toevoegen omdat de DB in gebruik is en het meer dan 90 miljoen rijen is; het zal iets zijn waar ik naar kijk tijdens een aantal downtime, maar nu wil ik het op deze manier laten draaien.

0

1 antwoord

Ik kan nog geen commentaar geven, dus hier is een 'antwoofd' ...

Weet je zeker dat het werkt? Als ik je beschrijving goed begrijp, heb je users.ip als CIDR-notatie in char of varchar en geoip.startip/endip als integer. Als zodanig heeft deze query geen manier om deze twee cofrect te vergelijken.

De juiste manier om dit te doen zou ook zijn

SELECT email, country 
FROM users INNER JOIN geoip ON INET_ATON(users.ip) 
BETWEEN geoip.startip AND geoip.endip

of

SELECT email, country 
FROM users INNER JOIN geoip ON users.ip 
BETWEEN INET_NTOA(geoip.startip) AND INET_NTOA(geoip.endip) 

which is the better one pretty much depending on which table is larger (mofe rows).

The best way to do this, though, would be to stofe users.ip as integer (of another column with integer interpretation).

2
toegevoegd
De eerste query werkt, maar is erg langzaam - is er een manier om dit te indexeren zodat het sneller werkt? Op dit moment, ongeacht wat, duurt het minstens 300-500ms per rij.
toegevoegd de auteur Michael Sweetser, de bron
Ja, en ook een index voor startip, endip en land samen.
toegevoegd de auteur Michael Sweetser, de bron
Ik heb de EXPLAIN-uitvoer aan de vraag toegevoegd. Op dit moment kan ik de betreffende IP-rij niet toevoegen, maar ik hoop dit tijdens een aantal downtime (de DB is in gebruik en is 90 miljoen rijen).
toegevoegd de auteur Michael Sweetser, de bron
Heeft u indexen op geoip.startip en geoip.endip?
toegevoegd de auteur Fox, de bron
Tenzij u het in WHERE, SORT, JOIN of dergelijke clausules gebruikt, heeft u geen index op land nodig ... Kunt u aangeven wat DESCRIBE your_query zegt? Ook kunt u proberen integer ip toe te voegen aan uw gebruikersentabel en proberen mee te doen met die kolom in plaats van CIDR ip? ( ALTER TABLE gebruikers ADD (intip int usigned), ADD INDEX intip (intip); UPDATE gebruikers SET intip = INET_ATON (ip); )
toegevoegd de auteur Fox, de bron
nou als wat je zegt correct is, dan zou de vraag ongeveer een halve dag duren. en het lijkt erop dat je het in de loop van de dag meerdere keren succesvol hebt uitgevoerd :) Dus ik vermoed dat je een soort van where- of limit-clausule gebruikt in je zoekopdracht. is dat correct? wijzigt u de vraag op een andere manier?
toegevoegd de auteur Fox, de bron