[MySQL] Wat is sneller?

[Verwijderd]

Hi,

Wat is in MySQL sneller?

1. sql = "SELECT `country` from `subnets` WHERE '$ip' > `ipBegin' AND '$ip' < `ipEnd';" // De ip address zijn in hex

Of

2. sql = "SELECT `country` from `subnets` WHERE ('$ip' & `mask`) = `subnet`;" // de ip address / subnets / masks zijn binary


De $ip moeten dan wel geslashes worden

De bedoeling is dat ik snel kan kijken of een ipaddress in een subnet valt en dan kijken in welk land dat subnet zit.

Ik heb ongeveer 740.000 subnetten!

[BasieP]

ik denk het bovenste, aangezien het een simpelere bewerking is. hangt ook van je cpu enzo af natuurlijk.

[Soultaker]

Argh! Dat gezeur van die PHP programmeurs altijd! Waarom zie ik nou nooit een C of Java programmeur die zich afvraagt wat sneller is?

Ik denk overigens dat er geen merkbaar verschil is. Er zijn een heleboel kleine dingen die meespelen, wat de uitwerking onvoorspelbaar maken. De tweede query is makkelijker te parsen, de vergelijking in de tweede query is misschien wat sneller uit te voeren, mits MySQL de IP-adressen intern als 32-bits getallen opslaat.

[ Voor 46% gewijzigd door Soultaker op 30-01-2003 14:00 ]

[Verwijderd]

BasieP schreef op 30 januari 2003 @ 13:55:
ik denk het bovenste, aangezien het een simpelere bewerking is. hangt ook van je cpu enzo af natuurlijk.

Dat weet ik nog niet zo: & is heel erg snel omdat dat op binary niveau word gedaan.

[D2k]

benchmarken zou ik zeggen
ik sluit mezelf aan bij Soultaker , denk niet dat er veel verschil tusen zal zitten

[Verwijderd]

Soultaker schreef op 30 januari 2003 @ 13:58:
Argh! Dat gezeur van die PHP programmeurs altijd! Waarom zie ik nou nooit een C of Java programmeur die zich afvraagt wat sneller is?

Ik denk overigens dat er geen merkbaar verschil is. Er zijn een heleboel kleine dingen die meespelen, wat de uitwerking onvoorspelbaar maken. De tweede query is makkelijker te parsen, de vergelijking in de tweede query is misschien wat sneller uit te voeren, mits MySQL de IP-adressen intern als 32-bits getallen opslaat.

Hi,

Dit gaat niet over php maar over MySQL want ik laat MySQL de vergelijkingen doen.

[Soultaker]

Verwijderd schreef op 30 januari 2003 @ 13:59:
Dat weet ik nog niet zo: & is heel erg snel omdat dat op binary niveau word gedaan.

Een EN-operatie is doorgaans net zo snel als een vergelijking: allebei een 1-cycle operatie van je ALU. De where-conditie van de eerste query is in principe uit te voeren met een optelling en een vergelijking, de where-conditie van de tweede query met een en-operatie an een vergelijking. In principe zou dat even snel moeten zijn, maar ik weet niet hoe MySQL de ip-adressen en masks intern representeert.

Verwijderd schreef op 30 januari 2003 @ 14:01:
Dit gaat niet over php maar over MySQL want ik laat MySQL de vergelijkingen doen.

Maar je bent wel een PHP programmeur of niet? Het ging me om de mentaliteit: de gemiddelde programmeur kan het geen reet schelen of de ene of andere variant sneller is, aangezien het verschil marginaal is (als het ueberhaupt merkbaar is).

[ Voor 29% gewijzigd door Soultaker op 30-01-2003 14:04 ]

[Verwijderd]

Soultaker schreef op 30 januari 2003 @ 13:58:
Argh! Dat gezeur van die PHP programmeurs altijd! Waarom zie ik nou nooit een C of Java programmeur die zich afvraagt wat sneller is?

Ik denk overigens dat er geen merkbaar verschil is. Er zijn een heleboel kleine dingen die meespelen, wat de uitwerking onvoorspelbaar maken. De tweede query is makkelijker te parsen, de vergelijking in de tweede query is misschien wat sneller uit te voeren, mits MySQL de IP-adressen intern als 32-bits getallen opslaat.

Ik gebruik geen getallen maar gewoon binary data

[Verwijderd]

Soultaker schreef op 30 januari 2003 @ 14:03:
[...]

Een EN-operatie is doorgaans net zo snel als een vergelijking: allebei een 1-cycle operatie van je ALU. De where-conditie van de eerste query is in principe uit te voeren met een optelling en een vergelijking, de where-conditie van de tweede query met een en-operatie an een vergelijking. In principe zou dat even snel moeten zijn, maar ik weet niet hoe MySQL de ip-adressen en masks intern representeert.


[...]

Maar je bent wel een PHP programmeur of niet? Het ging me om de mentaliteit: de gemiddelde programmeur kan het geen reet schelen of de ene of andere variant sneller is, aangezien het verschil marginaal is (als het ueberhaupt merkbaar is).

Ik ben OOK een php programeur ja (ik programeer in c & soms wat asm)

ik heb wel een table van 740.000 records en mischien dat mysql gewoon het eene sneller doet dan het andere en dat kan dan wel een 'leuk' verschilletje opleveren

[ACM]

Ik weet vrij zeker dat de eerste sneller is.
Tenminste, als je indices op je table hebt, bij de functionele vergelijking zal mysql geen index kunnen gebruiken

Btw, dit is ook nog een, meer leesbare, optie:
select * from tabel where $ip BETWEEN ipbegin AND ipeind;

[Verwijderd]

Verwijderd schreef op 30 januari 2003 @ 13:54:
1. sql = "SELECT `country` from `subnets` WHERE '$ip' > `ipBegin' AND '$ip' < `ipEnd';" // De ip address zijn in hex

Welk type heeft de kolom 'ipBegin' in de database?

Ik denk dat je best een ip address als een unsigned int opslaat.

[Verwijderd]

Met 740000 entries doet het er niet zo veel toe lijkt me. Het ene kan dan misschien wel 0,01 ms sneller zijn, maar doet dat er toe? Als je per sé sneller wil, dan bouw je gewoon je eigen datastructuur.

[Banpei]

Verwijderd schreef op 30 januari 2003 @ 15:45:
Met 740000 entries doet het er niet zo veel toe lijkt me. Het ene kan dan misschien wel 0,01 ms sneller zijn, maar doet dat er toe? Als je per sé sneller wil, dan bouw je gewoon je eigen datastructuur.

Met 740000 entries doet het er juist toe. Als de ene vergelijking er 0,01 ms langer over doet zal het bij 740000 entries 7,4 seconden sneller zijn. Vind dat nogal een verschil om eerlijk te zijn.

[TheDane]

Soultaker schreef op 30 januari 2003 @ 14:03:
[..]
Maar je bent wel een PHP programmeur of niet? Het ging me om de mentaliteit: de gemiddelde programmeur kan het geen reet schelen of de ene of andere variant sneller is, aangezien het verschil marginaal is (als het ueberhaupt merkbaar is).

[ Voor 3% gewijzigd door TheDane op 30-01-2003 16:31 ]

[brammetje]

TheDane schreef op 30 januari 2003 @ 16:30:
[...]

De gemiddelde programmeur wilt waarschijnlijk gewoon zorgen dat zijn app een beetje af komt.

Zoals vaker gezegd brengt een app 90% van de tijd door in 10% van de code.. Dat stukje ga je dan bekijken, zoekend naar evt. bottlenecks, en dat optimaliseer je.

[Olaf van der Spek]

Soultaker schreef op 30 January 2003 @ 14:03:
Een EN-operatie is doorgaans net zo snel als een vergelijking: allebei een 1-cycle operatie van je ALU. De where-conditie van de eerste query is in principe uit te voeren met een optelling en een vergelijking, de where-conditie van de tweede query met een en-operatie an een vergelijking. In principe zou dat even snel moeten zijn, maar ik weet niet hoe MySQL de ip-adressen en masks intern representeert.

De & hoeft maar een keer per queue gedaan te worden en dus is de tweede in het optimale geval sneller.

[Olaf van der Spek]

Banpei schreef op 30 januari 2003 @ 16:00:
Met 740000 entries doet het er juist toe. Als de ene vergelijking er 0,01 ms langer over doet zal het bij 740000 entries 7,4 seconden sneller zijn. Vind dat nogal een verschil om eerlijk te zijn.

Al had je er tien miljoen. Het verschil tussen de uitvoertijd van die twee where expessies is minimaal.

[ACM]

OlafvdSpek schreef op 30 januari 2003 @ 18:40:
De & hoeft maar een keer per queue gedaan te worden en dus is de tweede in het optimale geval sneller.

De & moet voor elke mask van elk record uitgerekend worden... en vergeleken met het subnet uit dat record.

De andere query kan een directe index-range lookup doen en zal daardoor vast sneller zijn.

[Verwijderd]

Banpei schreef op 30 januari 2003 @ 16:00:
[...]

Met 740000 entries doet het er juist toe. Als de ene vergelijking er 0,01 ms langer over doet zal het bij 740000 entries 7,4 seconden sneller zijn. Vind dat nogal een verschil om eerlijk te zijn.

Ik vind het erg knap van je dat het je gelukt is om te leren rekenen, maar waar haal je vandaan, dat ik zeg dat ik het verschil van 0,01 ms per regel van de tabel bedoel? Ik zeg dat het verschil tussen de twee queryuitvoertijden 0,01 ms is.

[Verwijderd]

ACM schreef op 30 januari 2003 @ 19:26:

[...]

De & moet voor elke mask van elk record uitgerekend worden... en vergeleken met het subnet uit dat record.

De andere query kan een directe index-range lookup doen en zal daardoor vast sneller zijn.

Ik heb mn ip addressen ge indext maar de index werd iets te groot (als ik het goed heb heb ik mn index toto over de 20 mb zien gaan) en mysql gooide zelf de index er uit! (de grote is nu 1 mb)

Ik gebruik nu optie 2.

Het probleem dat ik tegen kwam is dat mysql alleen bitwise functions uitvoert op bigint's en niet op strings

hij is al een paar uur bezig om alle subnets & subnet masks uit terekenen en in de database te gooien.


In PHP doen ik het als volgt:

lees bestand bla bla bla

PHP:

$ip1 = ipDec2Bin('192.168.99.0');
$ip2 = ipDec2Bin('192.168.99.255');

$mask = ~($ip1 ^ $ip2);
$subnet = $ip1 & $mask;

$maskDec = bindec(bin2strbin($mask));
$subnetDec = bindec(bin2strbin($subnet));

function ipDec2Bin($ipDec) {
  if (count($ipDecArray = explode('.', $ipDec)) != 4) {
    return false;
  }
  return chr($ipDecArray[0]) . chr($ipDecArray[1]) . chr($ipDecArray[2]) . chr($ipDecArray[3]);
}

function bin2strbin($data) {
/*

  ik type dit even uit mn hoofd en deze function weet ik niet meer.

  in ieder geval doet deze function het volgen

  als je bevoorbeeld 'a' geef dan geef deze '00101101' terug (klopt niet qua bits maar dat geeft niet voor het voorbeeld)

*/

}

Wat vinden jullie van mn code?

[esf]

De & moet voor elke mask van elk record uitgerekend worden... en vergeleken met het subnet uit dat record.

De andere query kan een directe index-range lookup doen en zal daardoor vast sneller zijn.

Hier ben ik het wel mee eens omdat bij de EN-variant MySQL kan optimaliseren en met een paar vergelijkingen een groot aantal waarden dat niet voldoet aan de zoekopdracht weglaten. Bij de & operator zal iedere range moeten worden berekend en dit zal altijd langer duren. Maar zoals al gezegd, het zal om enkele microseconden gaan...

[ACM]

Verwijderd schreef op 30 January 2003 @ 20:18:
Ik heb mn ip addressen ge indext maar de index werd iets te groot (als ik het goed heb heb ik mn index toto over de 20 mb zien gaan) en mysql gooide zelf de index er uit! (de grote is nu 1 mb)

Huh
Zou je niet es een nieuwere mysql installeren?

Als jij gewoon een index op je ipadresjes maakt zou dat toch echt moeten werken. En dan eentje ala:
create index van_tot ON subnets (ipbegin, ipend);
of twee losse op beide apart.

Ik heb nog nooit gehad dat mysql een index eruit gooide, dus je deed of zelf wat fout, of je data is niet goed oid...
Een index van 20MB is niet zo heel groot trouwens hoor, btw je zou er nog al es van kunnen profiteren als je je ipbegin en ipend als unsigned integers (dmv inet_ntoa en inet_aton kan je ze simpel converteren, wees dan wel zo verstandig je ingevoerde waarde te inet_aton-en, ipv de ipbegin en ipend) op slaat.

[Soultaker]

Verwijderd schreef op 30 januari 2003 @ 19:47:
Ik vind het erg knap van je dat het je gelukt is om te leren rekenen, maar waar haal je vandaan, dat ik zeg dat ik het verschil van 0,01 ms per regel van de tabel bedoel? Ik zeg dat het verschil tussen de twee queryuitvoertijden 0,01 ms is.

Het gaat er maar om dat het relatieve verschil in snelheid tussen de twee mogelijkheden minimaal is. Getalletjes vergelijken is toch wel goedkoper dan ze van de harde schijf halen, dus het effect van hoe je die getalletjes vergelijkt zal hooguit een procent ofzo (en dat is nog een optimistische schatting) bedragen. Niet echt een winst die een werkdag nadenktijd rechtvaardigd.

Zelfs als het dus 10 seconden scheelt, ben ik niet onder de indruk, als de gehele query een kwartier duurt.

Bij kleinere queries, waarbij zaken als het parsen van de query en het opstellen van het executieplan zwaarder meetellen, zijn beide opties zo snel dat ik me niet kan voorstellen dat 't je iets kan schelen of de ene 3 milliseconden sneller is.

OlafvdSpek schreef op 30 januari 2003 @ 18:40:
Al had je er tien miljoen. Het verschil tussen de uitvoertijd van die twee where expessies is minimaal.

Ik zit de thread achterstevoren te lezen. Dat is niet handig. Dit bedoelde ik dus maar.

brammetje schreef op 30 January 2003 @ 17:34:
De gemiddelde programmeur wilt waarschijnlijk gewoon zorgen dat zijn app een beetje af komt.

Zoals vaker gezegd brengt een app 90% van de tijd door in 10% van de code.. Dat stukje ga je dan bekijken, zoekend naar evt. bottlenecks, en dat optimaliseer je.

Het heeft ook te maken met het opdelen van een ontwerp in onafhankelijke componenten, en je steeds richten op een specifiek component. Als je met alle onderdelen van een enigszins complex systeem tegelijk rekening moet houden, wordt je helemaal gek.

[ Voor 37% gewijzigd door Soultaker op 31-01-2003 01:45 ]

[ACM]

Soultaker schreef op 31 January 2003 @ 01:38:
Bij kleinere queries, waarbij zaken als het parsen van de query en het opstellen van het executieplan zwaarder meetellen, zijn beide opties zo snel dat ik me niet kan voorstellen dat 't je iets kan schelen of de ene 3 milliseconden sneller is.

Tenzij het ene plan een index-range-scan en het andere een sequential-filtering-scan oplevert
MySQL is geen held in goed plannen, terwijl het ook nog eens min of meer onmogelijk wordt gemaakt door de 2e query.

* ACM hoopt nog steeds dat de topicstarter het voor elkaar krijgt een goede index te leggen op die velden en te ontdekken dat ik gelijk had
_{Ik hou er niet van ongelijk te hebben}

[Olaf van der Spek]

ACM schreef op 30 januari 2003 @ 19:26:
De & moet voor elke mask van elk record uitgerekend worden... en vergeleken met het subnet uit dat record.

De andere query kan een directe index-range lookup doen en zal daardoor vast sneller zijn.

Inderdaad, ik zat even niet op te letten.

[Janoz]

TheDane schreef op 30 januari 2003 @ 16:30:
[...]

[laat mode]

Een echte programeur houdt zich meer met macro optimalisaties bezig.

[TheDane]

Janoz schreef op 31 januari 2003 @ 11:37:

[...]


[laat mode]

Een echte programeur houdt zich meer met macro optimalisaties bezig.

dus de 'echte' programmeur van tegenwoordig heeft als motto: "we hebben een 2,5 gig dual processor met 2gig geheugen en een hdd van 64TB, dus we mogen inefficiente code schrijven" ??

sorry hoor, maar een beetje discipline om ook op meso en micro niveau fatsoenlijk werk af te leveren mag er best bij.

_{niet dat ik zelf een heilige ben overigens}

[Janoz]

TheDane schreef op 31 januari 2003 @ 11:43:
[...]


dus de 'echte' programmeur van tegenwoordig heeft als motto: "we hebben een 2,5 gig dual processor met 2gig geheugen en een hdd van 64TB, dus we mogen inefficiente code schrijven" ??

sorry hoor, maar een beetje discipline om ook op meso en micro niveau fatsoenlijk werk af te leveren mag er best bij.

_{niet dat ik zelf een heilige ben overigens}

Dat niet, maar met die overdaad kun je je prioriteiten beter bij goede code ipv snelle code leggen.

Ik zeg nergens dat er inefficiente code geschreven moet worden. Dit soort micro optimalisaties hebben meestal maar een minimale invloed op de uiteindelijke efficientie. Ze zijn afhankelijk van omstandigheden. Het kan best zijn dat op het testsysteem een bepaalde hack in een query wat voordelen oplevert, maar op een ander systeem kan dat averechts werken. En tot slot is dit soort optimalisatie hacks vaak een bron voor bugs.

Optimalizeren hoort al bij het ontwerp te gebeuren. Bijvoorbeeld door een goed DB ontwerp of datastructuren. Na afloop kun je natuurlijk kijken waar de bottlenecks zitten en vervolgens kijken of dit aangepast moet worden.

Tegenwoordig zie ik veel te vaak dat mensen al proberen te optimalizeren terwijl ze nog niet eens een werkend product hebben. IMHO heb je je prioriteiten dan verkeerd staan.

offtopic:
Van Speijck ondertitel?

[TheDane]

Janoz schreef op 31 januari 2003 @ 11:54:

[...]


Dat niet, maar met die overdaad kun je je prioriteiten beter bij goede code ipv snelle code leggen.

Ik zeg nergens dat er inefficiente code geschreven moet worden. Dit soort micro optimalisaties hebben meestal maar een minimale invloed op de uiteindelijke efficientie. Ze zijn afhankelijk van omstandigheden. Het kan best zijn dat op het testsysteem een bepaalde hack in een query wat voordelen oplevert, maar op een ander systeem kan dat averechts werken. En tot slot is dit soort optimalisatie hacks vaak een bron voor bugs.

Optimalizeren hoort al bij het ontwerp te gebeuren. Bijvoorbeeld door een goed DB ontwerp of datastructuren. Na afloop kun je natuurlijk kijken waar de bottlenecks zitten en vervolgens kijken of dit aangepast moet worden.

Tegenwoordig zie ik veel te vaak dat mensen al proberen te optimalizeren terwijl ze nog niet eens een werkend product hebben. IMHO heb je je prioriteiten dan verkeerd staan.

offtopic:
Van Speijck ondertitel?

ben 't 100% met je eens. Ik heb 't ook wat zwart-wit gesteld, als reactie op. Vaak is optimaliseren niet strikt noodzakelijk. Bij een webinterface bijvoorbeeld is de request-time vaak al vele malen langer dan de processing time. In mijn ervaring is 't dan ook niet bijzonder nuttig om daarop te optimaliseren ,. tenzij je bijvoorbeeld queries krijgt die er 10 seconden over doen om resultaat terug te geven

offtopic:
Van Speijck ondertitel ? ikke nie begrijp

[Olaf van der Spek]

Verwijderd schreef op 30 januari 2003 @ 14:04:
Ik gebruik geen getallen maar gewoon binary data

Kun je een 'describe' van die table posten?
Blob is geen goed idee, int wel.

[Verwijderd]

ACM schreef op 31 January 2003 @ 10:12:

[...]

Tenzij het ene plan een index-range-scan en het andere een sequential-filtering-scan oplevert
MySQL is geen held in goed plannen, terwijl het ook nog eens min of meer onmogelijk wordt gemaakt door de 2e query.

* ACM hoopt nog steeds dat de topicstarter het voor elkaar krijgt een goede index te leggen op die velden en te ontdekken dat ik gelijk had
_{Ik hou er niet van ongelijk te hebben}

Ik zag mn index oplopen toen mn php script de records aan het toevoegen was en in eens van de 20 mb ging hij naar de 5 kb om weer een klein beetje op te lopen.

??? verklaar maar ???

Das als ik het goed begrijp dan vinden jullie de eerste optie het beste omdat ik de ip addressen kan indexen? en dat een grooter dan vergelijking geen reken som is???

[Verwijderd]

OlafvdSpek schreef op 31 January 2003 @ 13:32:
[...]

Kun je een 'describe' van die table posten?
Blob is geen goed idee, int wel.

Maandag ben ik weer op kantoor en dan post ik het stukje wel