[mySQL] performance verbeteren van mySQL db

[Verwijderd]

Ik ben bezig met een statistiekenpakket. Hiervoor heb ik een tabel 'providers' waarin van de betreffende provider de ip-range staat (van ip1 tot ip2), daarnaast het land en de providernaam.

ip1 | ip2 | land | provider

Die tabel beslaat een klein 840.00 records en als ik een query erop loslaat duurtie een dikke drie seconde en das net ff te lang...

Ik heb hieronder de gebruikte query staan. ip adres is fictief. de toevoeging "AND landNaam = 'Nederland'" Verbetert nauwlijks de snelheid.

MySQL:

 
SELECT * FROM provider 
    WHERE ip1 <= INET_ATON('212.130.156.90')   
    AND ip2 >= INET_ATON('212.130.156.90')  
    AND landNaam = 'Nederland'

Zelf had ik het idee om uit de tabel 'provider' alle nederlandse providers eruit te halen en in een aparte tabel te zetten. Op die manier krijg ik wel een hogere performance en krijg ik binnen 0,3 s voor de nederlandse bezoekers resultaat, en vang ik zo'n 90% af, maar die 10% van de bezoekers die van andere locaties komen kan ik moeilijk drie seconde laten wachten...

Mijn vraag: heeft iemand een idee hoe ik die database sneller krijgt? Om voor elk land nu een aparte tabel te maken lijkt me niet echt alles... Of moet ik de tabel 'provider' opbreken in bijvoorbeeld 8 stukken op basis van de ip1 en ip2 en daar een tabel voor te zetten die bepaald wat de vervolg tabel gaat worden:

dus eerst kijken in mn indexTabel (ip1 en ip2 is dan de range van bijvoorbeeld de eerste 100.000 records van de oude tabel provider (dus krijg ik 9 records binnen deze tabel))

ip1 | ip2 | providerTabelID

en aan de hand van providerTabelID paktie de tabel waarbinnen het ip zich zou moeten bevinden... Is dit slim of kan dat beter en sneller? Wat is zo'n beetje max aan records met bovenstaande query binnen mysql? Of is de query sowieso al slecht? Ben benieuwd naar jullie ideeën...

[whoami]

Op welke velden heb je indexen liggen?

Die INET_ATON functie kan je query ook vertragen.

P&W FAQ - SQL

[ Voor 28% gewijzigd door whoami op 27-08-2003 20:18 ]

[Verwijderd]

Heb een index staan op ip1 en ip2, omdat daar infeite opgezocht moet worden dit leverde mij o,5 seconde op... (van 3,2 sec naar 2,7 sec)

Hier las ik altijd dat je door de db dingen moest laten opknappen waarvoor die gemaakt is, daarnaast is het vaak sneller om functies binnen mySQL te gebruiken dan dit via omwegen door PHP te laten doen... Maar ik kan wel ff proberen dit via PHP te doen en kijken of het sneller is....

[edit1] Heb ff snel een query uitgevoerd waarbij ik geen ipadres maar direct een longint heb ingevoerd en heb als resultaat 1,22 seconde, das een hele stap vooruit!

[edit2] Stom, maar moest natuurlijk ook mn landNaam veld indexen en zit daardoor op gemiddeld 0,4 sec per query, wat 1 veld indexen al niet uitmaakt...

Is het sowieso nog slim om mn db op te splitsen zoals ik voorstelde in de openingspost...

[ Voor 35% gewijzigd door Verwijderd op 27-08-2003 20:52 ]

[whoami]

Verwijderd schreef op 27 August 2003 @ 20:29:
Hier las ik altijd dat je door de db dingen moest laten opknappen waarvoor die gemaakt is, daarnaast is het vaak sneller om functies binnen mySQL te gebruiken

Een functie in het WHERE gedeelte van je query kan nadelig zijn voor de performance

Is het sowieso nog slim om mn db op te splitsen zoals ik voorstelde in de openingspost...

Dat hoef je niet te doen.

[Verwijderd]

hmmm dan ben ik al geholpen Bedankt!

[ACM]

Ik gok dat die functie in dit geval geen invloed heeft, gezien het feit dat ie op een invoerwaarde en niet op een kolom werkt...

Je zou een gecombineerde index op ip1, ip2 en land kunnen proberen, wellicht heeft dat zinvolle invloed op de performance, kijk iig naar de output van explain met je query.

[bigtree]

Omzetten naar een long is een goed idee. Maak daarnaast vooral een gekoppelde tabel 'landen' met een numerieke foreign key in je providers-tabel. Aangezien het land een constante is in je WHERE-clause, zal het gebruik van een landen-tabel sneller werken dan met een gedenormaliseerde oplossing met een (var)char in je providers-tabel.

Dan nog iets, zet ip1, ip2 en het land in de *zelfde* index. Dus niet een aparte index op de drie kolommen. Gaat een stuk sneller! Check deze pagina voor meer info.

En nog een laatste tip; voeg een 'LIMIT 0,1' toe aan je query! Op deze manier duurt de query gemiddeld nog maar de helft zo lang. Uitleg: Er wordt eerst op het eerste criterium uit je WHERE gezocht (ip1 <= INET_ATON('212.130.156.90')) en daar voldoet gemiddeld de helft aan. Als je data klopt, zal er altijd ten hoogste één record worden gevonden, dus kan MySQL wel stoppen zodra hij het laatste record heeft gevonden dat aan dat criterium voldoet.

Nu ik er zo over denk; het tweede criterium kan je volledig weglaten. Gewoon het record ophalen wat het dichtste 'onder' je criterium zit (met een LIMIT). Als de upper limit uit de range (ip2) 'boven' het te zoeken ip-adres zit, heb je een match op je range, anders niet. Op die manier zou je zelfs de index op ip2 kunnen laten schieten. Scheelt weer iets diskruimte. Succes!

[ Voor 5% gewijzigd door bigtree op 27-08-2003 21:34 . Reden: kromme zin verbeterd ]