[MYSQL] Relatie tussen ip adres en range

[Verwijderd]

Ik heb momenteel een tabel waarin bepaalde gegevens van alle page views van een web-site worden opgeslagen. Er wordt niet alleen per pagina een record toegevoegd, maar ook per afbeelding. Het ophalen van een pagina, kan er dus voor zorgen dat er meerdere records toegevoegd worden.
Verder heb heb ik een tabel met de gegevens (start_ip, end_ip, country_name) van +/- 47.000 ip ranges. Ik probeer een query te maken die de statistieken ophaalt, maar met landnaam (uit de ip range tabel) ipv alleen het ip adres.

Dit is de query:

code:

SELECT ip.country_name
FROM ip_ranges ip, statistics stat
WHERE ip.start_ip <= stats.ip_address AND stats.ip_address <= ip.end_ip

Het werkt wel, maar helaas werkt het heel traag. Als ik het limiet zet op 10, dan duurt het al 2.4 seconden. Momenteel bevat de page-view tabel +/ 50.000 records en het is de bedoeling dat dit via een web-pagina opgevraagd kan worden. Met deze snelheid zal dat dus niet lukken.

De start en eind velden hebben beide een index, dus daar ligt het helaas niet aan. Ik neem aan dat de query zo traag is omdat er op 2 velden vergeleken wordt, maar ik ik heb geen idee wat je hier aan kan doen?
Verder heb ik nog de between statement geprobeerd, maar daar wordt de query nog trager van.

Heeft er iemand misschien een idee hoe ik de query sneller kan maken, of is dit gewoon onmogelijk? Ik heb uiteraard hier en op google gezocht, maar ik heb helaas niks gevonden.
Het is trouwens wel mogelijk om de namen van de landen bij de page-views al toe te voegen, maar ik ben dan bang dat dit het ophalen van pagina's misschien kan vertragen. En ik ben gewoon benieuwd of ik deze query sneller kan maken

[zwippie]

edit:
niet doed genoeg gelezen

Kijk ook eens met welk geheugenmodel de database server loopt.

[ Voor 53% gewijzigd door zwippie op 28-05-2004 15:22 ]

[Gomez12]

Wat voor velden zijn de ip-adressen???
Als dit geen int is, dan zou ik gaan kijken of je het ip-adres niet of als geheel getal kan schrijven ( dus 192.168.1.1 wordt 192168001001) of als 4 int velden.

Want ik vermoed dat je ip-velden verkeerd zijn.

En hoeveel records zijn 47.000 ip-ranges??? Als dit er 47.000 zijn dan zou het geen enkel probleem moeten zijn. Zelfs niet met text.

[Verwijderd]

Gomez12 schreef op 28 mei 2004 @ 15:45:
Wat voor velden zijn de ip-adressen???
Als dit geen int is, dan zou ik gaan kijken of je het ip-adres niet of als geheel getal kan schrijven ( dus 192.168.1.1 wordt 192168001001) of als 4 int velden.

Want ik vermoed dat je ip-velden verkeerd zijn.

Ip adressen worden als unsigned 32 bit integers opgeslagen. In de query maak ik gebruik van inet_aton om het ip adres om te zetten in een int.

En hoeveel records zijn 47.000 ip-ranges??? Als dit er 47.000 zijn dan zou het geen enkel probleem moeten zijn. Zelfs niet met text.

De tabel bevat de volgende velden:
- start_ip
- end_ip
- country_name

Een range wordt dus in één record gedefinieerd.

[ Voor 3% gewijzigd door Verwijderd op 28-05-2004 16:04 ]

[Verwijderd]

foutje, sorry.

[ Voor 95% gewijzigd door Verwijderd op 28-05-2004 16:03 ]

[Verwijderd]

Los_Bastardos schreef op 28 mei 2004 @ 15:16:Dit is de query:

SELECT ip.country_name
FROM ip_ranges ip, statistics stat
WHERE ip.start_ip <= stats.ip_address AND stats.ip_address <= ip.end_ip

Ik neem aan dat dit een typ fout is?.

Je kan kijken of je eventueel between kan gebruiken, dat zou misschien sneller zijn.

code:

SELECT ip.country_name
FROM ip_ranges ip, statistics stats
WHERE stats.ip_address >= ip.start_ip AND stats.ip_address <= ip.end_ip

Dit geeft mijn persoonlijke voorkeur, geeft duidelijker aan tussen welke reeks hij moet liggen. Of het uit maakt in snelheid denk ik niet.

[ Voor 7% gewijzigd door Verwijderd op 28-05-2004 16:13 ]

[ripexx]

Doe eerste eens EXPLAIN voor je query zetten en dan deze te runnen in PHPmyAdmin of een ander progje. Dan zie je het execution plan en zie wat er gebeurt. Ik denk dat er of geen indexen op zitten of die join niet lekker gaat. EXPLAIN kan dat iig wel voor je achterhalen

offtopic:
Beter lezen

[ Voor 6% gewijzigd door ripexx op 28-05-2004 16:17 ]

[whoami]

Volgens de TS zitten er wel indexen op die velden, maar misschien zijn de statistics wel niet meer up to date.

Ow, je vois la problème:
Je hebt een cartesiaans product. Je selecteert nl. gegevens uit 2 tabellen, maar je joined die tabellen nergens.

[ Voor 40% gewijzigd door whoami op 28-05-2004 16:15 ]

[cameodski]

whoami schreef op 28 mei 2004 @ 16:15:
Ow, je vois la problème:
Je hebt een cartesiaans product. Je selecteert nl. gegevens uit 2 tabellen, maar je joined die tabellen nergens.

Als er in de ip_ranges tabel geen overlap zit, valt dat cartesiaanse product wel mee
Ik denk eerder dat MySQL die twee indexen niet zo goed tegelijkertijd kan gebruiken. Een index waarin zowel start_ip en end_ip zitten zou dat probleem wellicht op kunnen lossen.

[ Voor 11% gewijzigd door cameodski op 28-05-2004 16:22 ]

[whoami]

cameodski schreef op 28 mei 2004 @ 16:21:
[...]

Als er in de ip_ranges tabel geen overlap zit, valt dat cartesiaanse product wel mee

Hmm, idd. Toch vind ik het maar een rare query.

Ik denk eerder dat MySQL die twee indexen niet zo goed tegelijkertijd kan gebruiken. Een index waarin zowel start_ip en end_ip zitten zou dat probleem wellicht op kunnen lossen.

Dat zou ook wel eens kunnen.
Ik heb idd eens gelezen hier dat MySQL bij een query slechts 1 index kan gebruiken ? ( ). Als dat idd waar is, dan zou je idd beter een samengestelde index gebruiken.

[cameodski]

cameodski schreef op 28 mei 2004 @ 16:21:
[...]

Als er in de ip_ranges tabel geen overlap zit, valt dat cartesiaanse product wel mee
Ik denk eerder dat MySQL die twee indexen niet zo goed tegelijkertijd kan gebruiken. Een index waarin zowel start_ip en end_ip zitten zou dat probleem wellicht op kunnen lossen.

Mmm, bij nader inzien, geloof ik dat een samengestelde index ook niet zo goed gaat werken, maar hoe moet ik dat nu eens uitleggen

[Verwijderd]

whoami schreef op 28 mei 2004 @ 16:15:
Volgens de TS zitten er wel indexen op die velden, maar misschien zijn de statistics wel niet meer up to date.

Ow, je vois la problème:
Je hebt een cartesiaans product. Je selecteert nl. gegevens uit 2 tabellen, maar je joined die tabellen nergens.

Hij joint die twee tabellen wel. In zijn where statement koppelt hij die tabellen aan elkaar. Zo ie zo krijg je het resultaat terug van de 50 000 views.

cameodski schreef op 28 mei 2004 @ 16:29:
[...]

Mmm, bij nader inzien, geloof ik dat een samengestelde index ook niet zo goed gaat werken, maar hoe moet ik dat nu eens uitleggen

Zover ik op school gehad heb is een samengestelde index alleen geordend op het eerste veld van die index. Het zoeken op het eind ip zal als nog de gehele index door moeten lopen. Het levert dus geen extra snelheid op.

[ Voor 32% gewijzigd door Verwijderd op 28-05-2004 16:33 ]

[Verwijderd]

Bedankt voor de reacties

Ik heb idd al een between geprobeerd. Hierdoor werd het zelfs langzamer. De vergelijking heb ik ook anders geprobeerd (s <= i && s >= e ipv s <= i && i <= e), maar dat maakt ook niks uit.

Ik ga maar het maar met joins proberen

[cameodski]

Verwijderd schreef op 28 mei 2004 @ 16:30:
[...]


Zover ik op school gehad heb is een samengestelde index alleen geordend op het eerste veld van die index. Het zoeken op het eind ip zal als nog de gehele index door moeten lopen. Het levert dus geen extra snelheid op.

Mmm, in MSSQL wordt toch echt ook de rest gesorteerd, dus of je hebt iets verkeerds geleerd of je kennis is verouderd

Verwijderd schreef op 28 mei 2004 @ 16:33:
Bedankt voor de reacties

Ik heb idd al een between geprobeerd. Hierdoor werd het zelfs langzamer. De vergelijking heb ik ook anders geprobeerd (s <= i && s >= e ipv s <= i && i <= e), maar dat maakt ook niks uit.

Ik geloof niet dat between langzamer is, tenzij MySQL heel dom is.

Ik ga maar het maar met joins proberen

Joins proberen Veel sterkte, maar volgens mij was je daar min of meer al mee bezig

[whoami]

Verwijderd schreef op 28 mei 2004 @ 16:30:
[...]

Zover ik op school gehad heb is een samengestelde index alleen geordend op het eerste veld van die index. Het zoeken op het eind ip zal als nog de gehele index door moeten lopen. Het levert dus geen extra snelheid op.

Hmmm, het is eerst geordend op het meest linkse veld, en voor records met dezelfde waarden voor het linker-veld, is de index gesorteerd op het volgende veld, etc...

[whoami]

Verwijderd schreef op 28 mei 2004 @ 16:30:
[...]


Hij joint die twee tabellen wel. In zijn where statement koppelt hij die tabellen aan elkaar. Zo ie zo krijg je het resultaat terug van de 50 000 views.

In een WHERE clause kan je idd joinen, maar ik weet niet of het zo'n goed idee is om met <= en >= te joinen.

[Sjaaky]

Voor ideeën kun je hier kijken http://www.codeproject.com/csharp/IPLookupOptimise.asp

Alleen zou ik niet weten hoe je makkelijk een patricia trie in een database kan zetten.

[Verwijderd]

Verwijderd schreef op 28 mei 2004 @ 16:01:
[...]

Ip adressen worden als unsigned 32 bit integers opgeslagen. In de query maak ik gebruik van inet_aton om het ip adres om te zetten in een int.

In de query die je hier post zie ik geen inet_aton staan. Ik ken deze functie verder niet maar zorgt deze er misschien voor dat je indexen niet gebruikt worden?
Zoals eerdere posters al vroegen, doe eens een EXPLAIN van je query en kijk of er geen full table scans in voorkomen.

[cameodski]

whoami schreef op 28 mei 2004 @ 16:38:
[...]

In een WHERE clause kan je idd joinen, maar ik weet niet of het zo'n goed idee is om met <= en >= te joinen.

Het is in ieder geval niet zo fijn voor de performance, maar de vraag is wat het alternatief is. Het verbaast me wel dat MySQL daar zo'n grote moeite mee heeft.

[frickY]

En ik neem aan dat het doel van dit alles het achterhalen van het land van de bezoeker is?
Waarom dan niet de nodename achterhalen aan de hand van het IP, en hier het deel na de laatste punt van strippen?

Voor t geval dat je met PHP werkt kun je eens kijken naar gethostbyaddr

[Verwijderd]

cameodski schreef op 28 mei 2004 @ 16:36:
[...]
Joins proberen Veel sterkte, maar volgens mij was je daar min of meer al mee bezig

Doh
Ik was ff in de war

[Verwijderd]

Verwijderd schreef op 28 mei 2004 @ 16:46:
[...]

In de query die je hier post zie ik geen inet_aton staan. Ik ken deze functie verder niet maar zorgt deze er misschien voor dat je indexen niet gebruikt worden?
Zoals eerdere posters al vroegen, doe eens een EXPLAIN van je query en kijk of er geen full table scans in voorkomen.

Dank je. Ik zal een explain proberen

En ik neem aan dat het doel van dit alles het achterhalen van het land van de bezoeker is?
Waarom dan niet de nodename achterhalen aan de hand van het IP, en hier het deel na de laatste punt van strippen?

Ja, het is inderdaad de bedoeling om de landen te achterhalen.
Is het wel verstandig om de hostname te gebruiken? Wat moet je bijv. met com/net/org adressen doen?

[ Voor 32% gewijzigd door Verwijderd op 28-05-2004 18:20 ]

[Verwijderd]

Hmmm, het is eerst geordend op het meest linkse veld, en voor records met dezelfde waarden voor het linker-veld, is de index gesorteerd op het volgende veld, etc...

Dat bedoel ik ja. Maar omdat hij over reeksen praat is het begin adres altijd uniek naar mijn mening anders zou je een dubbele reeks gaan definieren.

[frickY]

Verwijderd schreef op 28 mei 2004 @ 17:56:

Ja, het is inderdaad de bedoeling om de landen te achterhalen.
Is het wel verstandig om de hostname te gebruiken? Wat moet je bijv. met com/net/org adressen doen?

Het lijkt me dat die niet kunnen voorkomen in een node-name
En anders doe je dan alsnog je IP-range query. Voor die paar keer dat het voor zal komen kan je wel die paar secondes wachten toch?
Of je zou ook nog de voorkeurs-taal van de browser kunnen opvragen uit de HTTP_ACCEPT_LANGUAGE-header.