[MySQL] Performance database

[HaBl]

Hallo,

Ik heb wat problemen met de snelheid van mijn database. Of eigenlijk maar één tabel. Ik ga er vanuit dat dit door de hoeveelheid data komt die er inmiddels in staat (125.000 records ongeveer en is stijgende), al zou MySQL prima met grote hoeveelheid records overweg moeten kunnen naar mijn idee.

De tabel waar het om gaat is mega simpel:

code:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| host  | varchar(255) | YES  | MUL | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Naja, opzich dus duidelijk wat erin staat, hostnames en ipadressen. Hierop draai ik deze query zeer regelmatig:

code:

SELECT host FROM addresses WHERE host='host.name.nl';

Deze query kost alleen iets van 0,10 tot 0,20 seconden. Dit is veel te lang aangezien ik dit soms in een script 40.000 keer uitvoer met verschillende hosts.

Nu had ik iets gelezen over FULLTEXT indexes, alleen krijg ik niet de indruk dat ik hier wat aan heb, aangezien het daarmee om matches gaat en ik gewoon een ja of een nee terug wil hebben om het maar simpel te zeggen.

Mijn vraag dus, heeft iemand een idee wat ik kan doen om de boel wat sneller te krijgen?

Bedankt alvast!

[chem]

Zet eens 'gewoon' een 'gewone' index op host? En zet hem eens van varchar om naar char?

[ Voor 31% gewijzigd door chem op 16-10-2006 17:19 ]

[elevator]

Als je nou je query zo maakt:

code:

SELECT 'host.name.nl';

Dan is ie een stukje sneller en doet hetzelfde - oftewel, is dit wel je volledige query ?

[sig69]

Ik vermoed dat de TS iets wil doen met statistieken ofzo, een SELECT COUNT(*) zou ook veel sneller kunnen zijn.

[MBV]

40.000 keer? Is er dan niet iets heel erg mis met je queries? Soms is het veel sneller om een moeilijke query 1x uit te voeren, dan een simpele 40.000. Wil je niet stiekem een GROUP BY, om het aantal keer te tellen dat een host voorkomt?

edit:
en ik haal al 300 aanslagen per minuut

* MBV gaat sparen voor DVORAK toetsenbord [/ME]

[ Voor 17% gewijzigd door MBV op 16-10-2006 17:24 ]

[TukkerTweaker]

elevator schreef op maandag 16 oktober 2006 @ 17:19:
Als je nou je query zo maakt:

code:

SELECT 'host.name.nl';

Dan is ie een stukje sneller en doet hetzelfde - oftewel, is dit wel je volledige query ?

scherp.

[HaBl]

Allen bedankt voor de snelle reacties.

Om te beginnen maar even een korte uitleg wat ik wil, dan is het wat duidelijker misschien. Ik ben een database aan het vullen met proxy adressen. Niet dat ik zo verslaafd ben aan die dingen, maar ik gebruik ze om te kunnen bannen op een irc-server (tsja, meestal zijn ze niet bedoelt voor een gezellige chat:)). Nu ben ik alleen ook bezig met een script dat automatisch hele lijsten met adressen in de database gaat stoppen. Om te voorkomen dat alle adressen er 10x dubbel in komen te staan heb ik die query staan om eerst te kijken of het adres niet al in de database zit.

Ik ben net even met die indexen aan het spelen gegaan, maar ik moet toegeven dat ik daar een beetje moeite mee heb. Tis voor het eerst dat ik daar mee aan de gang ga.

Uit de MySQL documentatie haalde ik het volgende voorbeeld:

code:

CREATE INDEX part_of_name ON customer (name(10));

Dit zou er voor moeten zorgen dat er een index moet komen op kolom name, maar alleen op de eerste 10 karakters. Eigenlijk begrijp ik dit niet zo goed, ik kan dit prima toepassen op mijn database natuurlijk, maar ik wil het ook graag begrijpen

[HaBl]

elevator schreef op maandag 16 oktober 2006 @ 17:19:
Als je nou je query zo maakt:

code:

SELECT 'host.name.nl';

Dan is ie een stukje sneller en doet hetzelfde - oftewel, is dit wel je volledige query ?

Nooit geweten dat dit werkte. Inderdaad is het een stuk sneller, maar aangezien ik 2 resultaten krijg bij het uitvoeren van een testje neem ik aan dat hij het op iedere tabel in de database uitvoert? In dat geval is dit niet de ideale oplossing

[chem]

Zet een UNIQUE op je Host kolom, insert je record en negeer de foutmelding.

[Arnaud]

TukkerTweaker schreef op maandag 16 oktober 2006 @ 17:29:
[...]


scherp.

Nee, dat is niet scherp.

De echte query geeft namelijk geen results terug als 'host.name.nl' niet in de tabel voorkomt en daar gaat het de TS nou juist om!

[HaBl]

chem schreef op maandag 16 oktober 2006 @ 17:35:
Zet een UNIQUE op je Host kolom, insert je record en negeer de foutmelding.

Klinkt opzich logisch, probleem is dan alleen dat ik niet eruit kan halen hoeveel records er daadwerkelijk zijn aangemaakt...

Overigens is het dan alleen met het toevoegen van de adressen wel sneller natuurlijk, alleen ooit moet ik de adressen ook weer kunnen uitlezen met het eerder gegeven voorbeeld, ook in dit geval kan het gebeuren dat die query vaker achter elkaar gedraaid wordt (afhankelijk van de activiteiten op de server dan natuurlijk).

Opzich zal die INDEX dan wellicht een oplossing zijn, ware het niet dat ik die nog niet helemaal begrijp.

Overigens las ik ook hierboven dat ik VARCHAR zou moeten wijzigen naar CHAR. Maar is het niet zo dat CHAR een vaste lengte heeft? Dan heb ik er niet heel veel aan aangezien die lengte niet altijd gelijk is.

[ Voor 50% gewijzigd door HaBl op 16-10-2006 17:57 ]

[chem]

In dat geval zou ik het andersom aanpakken; het is waarschijnlijker dat er meerdere malen een update wordt gedaan op een bestaand record (host) dan een nieuwe wordt toegevoegd.
Doe dan een update op je tables ( count = count + 1 oid), en als er 0 affected rows zijn => inserten.

Vergeet niet nog steeds een index toe te voegen op host, en er een fixed-length table van te maken.

[wboevink]

Erg plat gezegd: een index zet je op een kolom om het zoeken te vergemakkelijken/versnellen. Het is te vergelijken met een index in een boek. Dit geld voor where, like clauses maar ook voor joins.
Of een char veld sneller is als een varchar veld waag ik te betwijfelen aangezien de inhoud echt variabel is, maar ik heb geen idee hoe mySQL daarmee omgaat.

[ Voor 28% gewijzigd door wboevink op 16-10-2006 18:07 ]

[HaBl]

chem schreef op maandag 16 oktober 2006 @ 17:57:
In dat geval zou ik het andersom aanpakken; het is waarschijnlijker dat er meerdere malen een update wordt gedaan op een bestaand record (host) dan een nieuwe wordt toegevoegd.
Doe dan een update op je tables ( count = count + 1 oid), en als er 0 affected rows zijn => inserten.

Vergeet niet nog steeds een index toe te voegen op host, en er een fixed-length table van te maken.

Update wordt eigenlijk bijna nooit gedaan, hooguit een delete. Het meeste wordt er echt insert gedaan. Evengoed blijkt die index de oplossing te zijn. Ik heb die index net aangemaakt na wat manuals geraadpleegd te hebben. Alleen nogmaals, aan een CHAR heb ik volgens mij niets aangezien een hostname en ip-adres verschillende lengtes hebben en een CHAR een vaste lengte heeft.

Een quote uit één van de MySQL manuals:

Use a CREATE TABLE statement to specify the layout of your table:

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR is a good choice for the name, owner, and species columns because the column values will vary in length. The lengths of those columns need not all be the same, and need not be 20. You can pick any length from 1 to 255, whatever seems most reasonable to you. (If you make a poor choice and it turns out later that you need a longer field, MySQL provides an ALTER TABLE statement.)

Als ik dat zo lees heb ik die varchar nodig.

Erg plat gezegd: een index zet je op een kolom om het zoeken te vergemakkelijken/versnellen. Het is te vergelijken met een index in een boek. Dit geld voor where, like clauses maar ook voor joins.
Of een char veld sneller is als een varchar veld waag ik te betwijfelen aangezien de inhoud echt variabel is, maar ik heb geen idee hoe mySQL daarmee omgaat.

Dat maakt een hoop duidelijk Inmiddels heeft het bewezen bij mij dat het werkt, die query's zijn nu heel snel weer.

Bedankt allen! Ik kan weer verder!

[Koelkasten]

Weet ook niet welke versie van MySQL je draait maar anders kun je ook hier eens naar kijken

http://dev.mysql.com/doc/.../insert-on-duplicate.html

[MSalters]

HaBl schreef op maandag 16 oktober 2006 @ 18:22:
die index [blijkt] de oplossing te zijn.

Dan nog wil je een SELECT COUNT(*) doen; dat is namelijk over een jaar nog te snappen. Je wil niet weten wat host is, dat weet je al. Je wil weten hoe vaak die host voorkomt, 0 of 1.

[D4V3]

Even wat opheldering over char en varchar. Je kan makkelijk char gebruiken i.p.v. varchar. Het is een misvatting dat char een vast aantal karakters heeft en varchar een variabel aantal. Het verschil zit hem echter in de manier waarin. Bij een char(255) worden er 255b gereserveerd voor een waarde. Of deze waarde nu daadwerkelijk 255 of 10 is maakt niet uit, de ruimte is gereserveerd voor 255 chars. Bij varchar echter zijn de bytes variabel waardoor het de ene keer 10b lang is en de andere keer 255b. Omdat deze niet gereserveerde ruimte moet schuiven is het over het algemeen sneller om een vaste grootte aan te houden. Een nadeel hiervan kan zijn dat de database meer ruimte in neemt. Ik vraag me ook af of 255 niet iets te veel van het goede is voor een hostname, dat kan toch wel korter?

[Gomez12]

HaBl schreef op maandag 16 oktober 2006 @ 18:22:
[...]


...
Alleen nogmaals, aan een CHAR heb ik volgens mij niets aangezien een hostname en ip-adres verschillende lengtes hebben en een CHAR een vaste lengte heeft.

Een quote uit één van de MySQL manuals:


[...]


Als ik dat zo lees heb ik die varchar nodig.

Voor zover ik weet zijn er 2 grote verschillen tussen varchar en char.
1 : Varchar is over het kleiner dan char ( want een varchar van 20 tekens met maar 5 tekens gevuld gebruikt maar 6 tekens terwijl char altijd 20 tekens gebruikt ) maar ja hdd-ruimte is er over het algemeen genoeg. Vind ik nooit zo zwaar wegen
2 : Char is sneller dan varchar bij grotere bestanden ( tenminste als je alles varchar of alles char hebt ). Bij char records kan je dbase heel snel naar het volgende record skippen ( het volgende record ligt altijd 20 tekens verder ) terwijl er bij varchar per record gekeken moet worden waar het eindigt en het volgende begint.

Maar deze verschillen komen pas echt tot uiting bij grote dbases die zwaar belast worden.
Maar persoonlijk vind ik varchar alleen maar onhandiger dan char, ruimte is er genoeg.

En ik ben nog steeds benieuwd wat er gebeurt als ik 1.000.000 records heb en in record 2 ( relatief gezien ) voeg ik 100 bytes toe, wat gebeurt er nu?
A : Wordt het totale record naar het einde verplaatst en daar opnieuw weggeschreven?
B : Worden er 998 records verschoven?
C : Word het record gefragmenteerd?

Terwijl er met char gewoon altijd ruimte is voor die 100 bytes. De dbase hoeft niets met het hele record te doen alleen maar te kijken of die 100 bytes past of niet past.

[cytherea]

Wat ik zou doen is een 'summary' tabel bijhouden.
Dan bereken je een keer per dag/uur of wat dan ook de verschillende counts die je nodig hebt en leest die iedere keer uit.
Het doel waarvoor je het beschrijft lijkt me niet erg dynamisch dat het iedere seconde moet kloppen.

In MySQL 5 kun je dit oplossen d.m.v triggers, in MySQL < 5 moet je dat zelf in een script zetten en die met een cronjob laten draaien.

Andere dingen die je kunt proberen is het geheugengebruik van MySQL anders instellen zodat hij meer index opslag krijgt, maar dan heeft je server ook behoorlijk wat ram nodig, denk aan 2/3 GB minimaal.

Zoveel records doorspitten en berekenen kost nou eenmaal tijd. Na een tijdje kan het niet meer sneller hoeveel indexes je ook gebruikt. Dus ik zou gaan voor mijn oplossing, scheelt sowieso 40.000 keer berekenen wat je in je post noemde.

Suc6

edit:

Verder even nakijken welke engine je gebruikt, Myisam, innodb enz... De een is sneller voor de ene toepassing. Myisam is naar mijn weten de snelste maar mist transactie support.

[ Voor 9% gewijzigd door cytherea op 18-10-2006 00:46 ]

[HaBl]

Zozo, dat zijn nog een hoop reacties na mijn succesmelding. Toch maar even response hierop aangezien het altijd beter kan en er wat intressante dingen verteld worden .

Koelkasten schreef op dinsdag 17 oktober 2006 @ 16:24:
Weet ook niet welke versie van MySQL je draait maar anders kun je ook hier eens naar kijken

http://dev.mysql.com/doc/.../insert-on-duplicate.html

Dit gaat puur over het inserten van de data. Daar heb ik er ook wel last van, maar het uitlezen is eigenlijk belangrijker. Verderop in deze post een uitleg hierover. Ik ben daar niet helemaal duidelijk in geweest denk ik.

D4V3 schreef op dinsdag 17 oktober 2006 @ 23:31:
Even wat opheldering over char en varchar. Je kan makkelijk char gebruiken i.p.v. varchar. Het is een misvatting dat char een vast aantal karakters heeft en varchar een variabel aantal. Het verschil zit hem echter in de manier waarin. Bij een char(255) worden er 255b gereserveerd voor een waarde. Of deze waarde nu daadwerkelijk 255 of 10 is maakt niet uit, de ruimte is gereserveerd voor 255 chars. Bij varchar echter zijn de bytes variabel waardoor het de ene keer 10b lang is en de andere keer 255b. Omdat deze niet gereserveerde ruimte moet schuiven is het over het algemeen sneller om een vaste grootte aan te houden. Een nadeel hiervan kan zijn dat de database meer ruimte in neemt. Ik vraag me ook af of 255 niet iets te veel van het goede is voor een hostname, dat kan toch wel korter?

Ok, dan ben ik verkeerd ingelicht Ik heb het ergens op een onofficiele site gelezen. Volgende keer toch maar weer in de documentatie van mysql zelf zoeken. Ik zal het in dat geval toch gaan aanpassen.

Overigens is 255 voor een hostname niet teveel naar mijn idee. Goed, tis wel erg lang, maar de mogelijkheid bestaat zeker dat een hostname zo lang wordt. Denk aan host.sub.domein.tld, in theorie zou deze hele reeks volgens mij nog langer als 255 tekens kunnen zijn.

cytherea schreef op woensdag 18 oktober 2006 @ 00:44:
Wat ik zou doen is een 'summary' tabel bijhouden.
Dan bereken je een keer per dag/uur of wat dan ook de verschillende counts die je nodig hebt en leest die iedere keer uit.
Het doel waarvoor je het beschrijft lijkt me niet erg dynamisch dat het iedere seconde moet kloppen.

In MySQL 5 kun je dit oplossen d.m.v triggers, in MySQL < 5 moet je dat zelf in een script zetten en die met een cronjob laten draaien.

Andere dingen die je kunt proberen is het geheugengebruik van MySQL anders instellen zodat hij meer index opslag krijgt, maar dan heeft je server ook behoorlijk wat ram nodig, denk aan 2/3 GB minimaal.

Zoveel records doorspitten en berekenen kost nou eenmaal tijd. Na een tijdje kan het niet meer sneller hoeveel indexes je ook gebruikt. Dus ik zou gaan voor mijn oplossing, scheelt sowieso 40.000 keer berekenen wat je in je post noemde.

Suc6

edit:

Verder even nakijken welke engine je gebruikt, Myisam, innodb enz... De een is sneller voor de ene toepassing. Myisam is naar mijn weten de snelste maar mist transactie support.

Ehm.. Ik snap niet helemaal hoe je het bedoelt, maar in principe is het juist belangrijk dat die informatie iedere seconden klopt. Bij het invoeren van de data maakt het opzich niet zoveel uit, dat voer ik maar 1x per dag uit en het is hierbij ook geen ramp als het iets langer duurt, als het maar geen minuten tot uren gaan worden. Ik kwam er alleen toevallig bij dit script om in te voeren erachter hoe traag het ging. Maar bij het uitlezen van de data is het wel belangrijk. Je moet het zo zien dat er vrij regelmatig connectie naar de server gemaakt wordt, waarop vervolgens gecontrolleerd moet worden of het ip-adres of host in de database voorkomt. Als het een proxy betrefd moet deze tegen gehouden worden. Nadeel is dat mensen die kwaad willen met proxy's (waar proxy's op IRC helaas nogal veel voor gebruikt worden) vaak met 500 tot 1000 adressen tegelijk komen. Dan moet die query dus in een zeer korte tijd zeer vaak gedraaid worden. Dan is het uiteraard ook heel belangrijk dat de informatie wel klopt, anders worden ze door gelaten.

Goed, al deze adressen tegelijk tegenhouden lukt zowiezo niet aangezien daarvoor teveel proxy adressen zijn die ik niet in mijn database heb, maar in samenwerking met de bestaande proxyscanners kan het wel een hoop schelen. Maar dit even terzijde.

[Verwijderd]

Gomez12 schreef op dinsdag 17 oktober 2006 @ 23:37:
Voor zover ik weet zijn er 2 grote verschillen tussen varchar en char.
1 : Varchar is over het kleiner dan char ( want een varchar van 20 tekens met maar 5 tekens gevuld gebruikt maar 6 tekens terwijl char altijd 20 tekens gebruikt ) maar ja hdd-ruimte is er over het algemeen genoeg. Vind ik nooit zo zwaar wegen
2 : Char is sneller dan varchar bij grotere bestanden ( tenminste als je alles varchar of alles char hebt ). Bij char records kan je dbase heel snel naar het volgende record skippen ( het volgende record ligt altijd 20 tekens verder ) terwijl er bij varchar per record gekeken moet worden waar het eindigt en het volgende begint.

Het tweede verschil betwijfel ik. Dit kan ook bij het gebruik van een varchar zolang er maar een index beschikbaar is waar gebruik van gemaakt kan worden. In de index is dan namelijk de locatie van iedere record te vinden. Als maar een klein deel van de char-kolom wordt gebruikt kan het zelfs nadelig zijn aangezien er veel meer data ingelezen moet worden (meer I/O).

Maar deze verschillen komen pas echt tot uiting bij grote dbases die zwaar belast worden.

Ervaring
Ik heb namelijk mijn twijfels...

En ik ben nog steeds benieuwd wat er gebeurt als ik 1.000.000 records heb en in record 2 ( relatief gezien ) voeg ik 100 bytes toe, wat gebeurt er nu?
A : Wordt het totale record naar het einde verplaatst en daar opnieuw weggeschreven?
B : Worden er 998 records verschoven?
C : Word het record gefragmenteerd?

Data in een database wordt vaak (voor zover ik weet) opgeslagen m.b.v. B-trees. Zodra de data niet meer in een node past (string in een varchar wordt langer) wordt deze node gesplits in twee nodes. Hierbij is het mogelijk dat één van de nodes op een totaal andere plek wordt weggeschreven op disk. Dit probleem doet zich overigens ook voor wanneer je een record midden in een tabel wilt toegevoegen.

Ter info (source):

For InnoDB tables, it is also true that CHAR columns take more space on average than VARCHAR. But there is no retrieval speed advantage for InnoDB as there is with MyISAM, because the InnoDB engine implements storage for both CHAR and VARCHAR in a similar way. In fact, retrieval of CHAR values might be slower because on average they require more information to be read from disk.

[ Voor 10% gewijzigd door Verwijderd op 18-10-2006 08:56 . Reden: Nog wat extra info ]

[cytherea]

HaBl schreef op woensdag 18 oktober 2006 @ 01:20:
Zozo, dat zijn nog een hoop reacties na mijn succesmelding. Toch maar even response hierop aangezien het altijd beter kan en er wat intressante dingen verteld worden .

Nouja, misschien heb je nog iets aan mijn info

HaBl schreef op woensdag 18 oktober 2006 @ 01:20:
Ehm.. Ik snap niet helemaal hoe je het bedoelt, maar in principe is het juist belangrijk dat die informatie iedere seconden klopt. Bij het invoeren van de data maakt het opzich niet zoveel uit, dat voer ik maar 1x per dag uit en het is hierbij ook geen ramp als het iets langer duurt, als het maar geen minuten tot uren gaan worden. Ik kwam er alleen toevallig bij dit script om in te voeren erachter hoe traag het ging. Maar bij het uitlezen van de data is het wel belangrijk. Je moet het zo zien dat er vrij regelmatig connectie naar de server gemaakt wordt, waarop vervolgens gecontrolleerd moet worden of het ip-adres of host in de database voorkomt. Als het een proxy betrefd moet deze tegen gehouden worden. Nadeel is dat mensen die kwaad willen met proxy's (waar proxy's op IRC helaas nogal veel voor gebruikt worden) vaak met 500 tot 1000 adressen tegelijk komen. Dan moet die query dus in een zeer korte tijd zeer vaak gedraaid worden. Dan is het uiteraard ook heel belangrijk dat de informatie wel klopt, anders worden ze door gelaten.

Ja wat ik er nu uit begrijp is dat het echt om de info uit de database gaat die je uit wil lezen, dan is mijn oplossing niet zo handig.

Ik dacht dat meer ging om berekeningen met de data. Bijvoorbeeld als je een count wil uitvoeren kun je een aparte tabel bijhouden waar deze count alsvast instaat en iedere keer als er eentje wordt toegevoegd de count++ wordt gedaan. Dat scheelt de count query iedere keer opnieuw uitvoeren.

Maar dat is in andere situaties handig dan degene die jij beschrijft

[HaBl]

cytherea schreef op woensdag 18 oktober 2006 @ 14:12:
[...]


Nouja, misschien heb je nog iets aan mijn info

Ik ben je dankbaar

[...]


Ja wat ik er nu uit begrijp is dat het echt om de info uit de database gaat die je uit wil lezen, dan is mijn oplossing niet zo handig.

Ik dacht dat meer ging om berekeningen met de data. Bijvoorbeeld als je een count wil uitvoeren kun je een aparte tabel bijhouden waar deze count alsvast instaat en iedere keer als er eentje wordt toegevoegd de count++ wordt gedaan. Dat scheelt de count query iedere keer opnieuw uitvoeren.

Maar dat is in andere situaties handig dan degene die jij beschrijft

Ja inderdaad, het idee is inderdaad goed. Die count gebruik ik overigens ook wel, maar zeer sporadisch, dus dat zal geen problemen opleveren. Maar ik houd het evengoed in mijn achterhoofd voor eventuele toekomst plannen!

[Kees]

Als het gaat om irc-servers en proxies, dan kun je toch net zo goed met ipadressen werken? Dus gewoon een (unsigned) 32 bits integer opslaan zou qua performance ook al een stuk beter moeten werken

[CH4OS]

Kees schreef op woensdag 18 oktober 2006 @ 18:42:
Als het gaat om irc-servers en proxies, dan kun je toch net zo goed met ipadressen werken? Dus gewoon een (unsigned) 32 bits integer opslaan zou qua performance ook al een stuk beter moeten werken

Dan zit je met het feit dat je het IP-adres moet updaten in je tabel, als een proxy van IP-adres veranderd... Dan blijf je als het ware dwijlen met de kraan open. Niet dat dit zo verschrikkelijk vaak gebeurd, maar de mogelijkheid is er wel...

Ik kan aannemen dat een proxy altijd wél dezelfde hostname bevat, dan vul je die ook in, in je tabel zodat je daar eventueel ook op zou kunnen bannen en / of een IP-lookup er op doen...

[ Voor 5% gewijzigd door CH4OS op 19-10-2006 08:36 ]

[Verwijderd]

chem schreef op maandag 16 oktober 2006 @ 17:57:
In dat geval zou ik het andersom aanpakken; het is waarschijnlijker dat er meerdere malen een update wordt gedaan op een bestaand record (host) dan een nieuwe wordt toegevoegd.
Doe dan een update op je tables ( count = count + 1 oid), en als er 0 affected rows zijn => inserten.

Vergeet niet nog steeds een index toe te voegen op host, en er een fixed-length table van te maken.

Misschien nog wat vlotter: Trigger aanmaken die exact het omgekeerde doet-> on error -> update +1

[Olaf van der Spek]

chem schreef op maandag 16 oktober 2006 @ 17:57:
In dat geval zou ik het andersom aanpakken; het is waarschijnlijker dat er meerdere malen een update wordt gedaan op een bestaand record (host) dan een nieuwe wordt toegevoegd.
Doe dan een update op je tables ( count = count + 1 oid), en als er 0 affected rows zijn => inserten.

Vergeet niet nog steeds een index toe te voegen op host, en er een fixed-length table van te maken.

Dan kun je toch beter insert ... on duplicate key update ... doen? Dan is één query voldoende en kun je meer (onafhankelijke) rows per keer updaten.

[...]

Waarom voorkom je de foutmelding niet door insert ignore ... te gebruiken?

[ Voor 17% gewijzigd door Olaf van der Spek op 19-10-2006 14:24 ]

[Verwijderd]

HaBl schreef op woensdag 18 oktober 2006 @ 01:20:


[...]

Overigens is 255 voor een hostname niet teveel naar mijn idee. Goed, tis wel erg lang, maar de mogelijkheid bestaat zeker dat een hostname zo lang wordt. Denk aan host.sub.domein.tld, in theorie zou deze hele reeks volgens mij nog langer als 255 tekens kunnen zijn.

Dit lijkt me wel erg veel, gooi eens een

SQL:

SELECT LENGTH(host) FROM `adresses` ORDER BY LENGTH(host) DESC LIMIT 0,1

over je db heen? volgens mij benadert de langste hostname niet eens de 255

[ Voor 4% gewijzigd door Verwijderd op 19-10-2006 15:44 ]

[WimB]

Verwijderd schreef op donderdag 19 oktober 2006 @ 15:44:
[...]

Dit lijkt me wel erg veel, gooi eens een

SQL:

SELECT LENGTH(host) FROM `adresses` ORDER BY LENGTH(host) DESC LIMIT 0,1

over je db heen? volgens mij benadert de langste hostname niet eens de 255

Kan dit niet zo:

SQL:

SELECT MAX(LENGTH(host)) FROM `adresses`

[Verwijderd]

WimB schreef op donderdag 19 oktober 2006 @ 15:50:
[...]


Kan dit niet zo:

SQL:

SELECT MAX(LENGTH(host)) FROM `adresses`

Ow idd waarschijnlijk wel , niet aan gedacht

[The - DDD]

Is het niet het makkelijkst om voor je batch een count over de hele tabel te doen en vervolgens een insert met een "ON DUPLICATE KEY UPDATE". Dan doe je ipv een insert een update, die effectief niks wijzigt. Na je batch opnieuw een count en het verschil nemen.

Het halveert minstens je aantal queries en je weet exect hoeveel je er toegevoegd hebt.

[gvdh81]

D4V3 schreef op dinsdag 17 oktober 2006 @ 23:31:
Ik vraag me ook af of 255 niet iets te veel van het goede is voor een hostname, dat kan toch wel korter?

Ter info

quote: http://www.faqs.org/rfcs/rfc2181.html

A full domain name is limited to 255 octets (including the separators). The zero length full name is defined as representing the root of the DNS tree, and is typically written and displayed as ".".

[Voutloos]

Je zou zelfs kunnen overwegen om die tabel te vullen met md5 hashes van domeinen. Is alles meteen een constante lengte en hoef je ook niet te kibbelen over hoeveel karakters het wel niet moet zijn. Gewoon een kolom voor de hash en eentje voor het tellertje. Sim-pel \o/

[HaBl]

GJ-tje schreef op donderdag 19 oktober 2006 @ 08:35:
[...]
Dan zit je met het feit dat je het IP-adres moet updaten in je tabel, als een proxy van IP-adres veranderd... Dan blijf je als het ware dwijlen met de kraan open. Niet dat dit zo verschrikkelijk vaak gebeurd, maar de mogelijkheid is er wel...

Ik kan aannemen dat een proxy altijd wél dezelfde hostname bevat, dan vul je die ook in, in je tabel zodat je daar eventueel ook op zou kunnen bannen en / of een IP-lookup er op doen...

In principe heeft Kees wel gelijk. In de meeste gevallen zullen de hosts ook mee veranderen aangezien het vaak gewoon om hostnames van diverse ISP's gaat. In mijn geval heb ik bijvoorbeeld xxx-xxx-xxx-xxx.quicknet.nl als host, als mijn IP veranderd, veranderd mijn host ook. Dit geld voor de meeste proxy's ook.

Daarbij werken proxy's in de meeste gevallen maar tijdelijk. Daarom zal ik de adressen ook nooit updaten. Ik voeg er alleen nieuwe aan toen. De volgende stap is ook om om de zoveel tijd de adressen van een bepaalde leeftijd na te lopen of ze nog wel actief zijn en zoniet deze te verwijderen. Maar dit zijn toekomstplannen, waarschijnlijk zullen dit ook wel redelijk zware scripts opleveren

Maargoed, ik geloof dat we nu een beetje offtopic gaan. Dus ik laat het hierbij.

Edit: Ok, ik had niet gezien dat er inmiddels 2 pagina's waren aan dit topic, dus miste wat reacties.

Voutloos schreef op donderdag 19 oktober 2006 @ 22:24:
Je zou zelfs kunnen overwegen om die tabel te vullen met md5 hashes van domeinen. Is alles meteen een constante lengte en hoef je ook niet te kibbelen over hoeveel karakters het wel niet moet zijn. Gewoon een kolom voor de hash en eentje voor het tellertje. Sim-pel \o/

Opzich een heel goed idee dit, alleen krijg ik dan (zoals ik hierboven eigenlijk al zei) een probleem in de toekomst, als ik wil gaan opschonen. Als het een MD5 hash is, kan ik niet meer controleren of de adressen nog actief zijn

[ Voor 21% gewijzigd door HaBl op 20-10-2006 15:50 ]

[wboevink]

HaBl schreef op vrijdag 20 oktober 2006 @ 15:45:
Opzich een heel goed idee dit, alleen krijg ik dan (zoals ik hierboven eigenlijk al zei) een probleem in de toekomst, als ik wil gaan opschonen. Als het een MD5 hash is, kan ik niet meer controleren of de adressen nog actief zijn

Dan zet je toch zowel de hash als de host in de tabel.
De hash gebruik je omdat het zoeken een stuk sneller gaat (waarschijnlijk)
En de host gebruik je voor de controles.
Of de hashes in een aparte tabel die je later met de host kan joinen voor de controles.
Of de ipnummers als int erbij opslaan en deze eens in de zoveel tijd updaten.

[ Voor 6% gewijzigd door wboevink op 20-10-2006 17:21 ]

[gvdh81]

Of enkel de hashes, en zodra je een request krijgt van het domein, moet je toch een SQL query doen, dus kun je net zo goed een timestamp veld updated met "laatste activiteit" ozid...