[Alg] 2miljoen records, DB, indexen enz.

[Nexopheus]

Ik heb een csv-file van ongeveer 2,6 miljoen records, deze moeten dus in een database. De data in de database zal alleen opgevraagd worden, geen inserts en updates.

In access is geen optie, heb ik geprobeerd maar het uitvoeren van een simpele select duurt veel te lang (lees : > 60 sec).

Nu heb ik ook mysql geprobeerd, alleen krijg ik de csv niet geimporteerd, duurt veel te lang (lees : > 10 uur).

Iemand hier ideeen over ????

Verder ben ik benieuwd hoe ik het beste om kan gaan met indexen.
er zal altijd gezocht worden op 1 record waarbij een waarde tussen 2 anderen in ligt. Deze 2 andere waarden zijn beide PK.
Voorbeeldje

SQL:

select country from tblLocations where ipNo between ipFROM and ipTo;

Ik neem aan dat de indexen op de ipFROM en ipTO kolom het beste zijn ?

Hoop dat het duidelijk is wat de moeilijkheid is!

[justmental]

10 uur voor die mysql import vind ik veel, dat komt neer op 72 records per seconde, wat voor methode heb je daarvoor gebruikt?

Een individuele index op die velden lijkt me een goed idee.
Let wel op dat je door die PK waarschijnlijk al een index erbij krijgt.

[ Voor 17% gewijzigd door justmental op 13-11-2003 22:58 ]

[ACM]

MySQL kan niet meerdere indices tegelijk gebruiken op 1 tabel tijdens een query, dus wellicht is een gecombineerde index op ipFrom en ipTo een optie.

Wat ik niet snap is hoe je het voor elkaar krijgt dat dat 10 uur duurt, of het is een extreem traag systeem, of je gebruikt een niet zo handige manier om de boel te importeren?

[Varienaja]

Access wil niet?

Raar. Een collega van me heeft pas een CSV met 1.1 miljoen records in 8 minuutjes in een Access-DB ingelezen. Selectiequeries geven resultaat in een wip (mits geindexeerd).

Normaal zou ik Access niet aanprijzen, maar mede gezien je gekloot met mySQL lijkt het me dat je ergens iets fout doet.. :-/

[Tomatoman]

Een index versnelt zoekwerk in records, maar vertraagt het importeren en muteren van records. Als je de records in een tabel wilt stoppen, kun je het beste de index van de tabel droppen, dan alle records importeren en tenslotte een nieuwe index maken.

Om de query te versnellen kun je proberen hem in tweeën te knippen, dat lost meteen je probleem met twee indexen op. Je zoekt eerst alle records die groter dan of gelijk zijn aan ipFROM en in dat queryresultaat zoek je naar alle records die kleiner dan of gelijk zijn aan ipTO. Ik weet niet of dit sneller gaat (misschien gaat het wel veel langzamer), maar het is een poging waard.

[edit]
@Annie: ik zag het toen ik het zat na te lezen en veranderde het net toen jij met je reactie kwam. Je hebt helemaal gelijk.

[ Voor 69% gewijzigd door Tomatoman op 13-11-2003 23:36 . Reden: tomatoman zat te pitten ]

[Annie]

tomatoman schreef op 13 november 2003 @ 23:25:
Een index versnelt zoekwerk en mutaties van records

Uh, bedoel je niet dat een index het zoekwerk naar een record wat gemuteerd moet worden kan versnellen? Het muteren zelf wordt juist vertraagd door het bijwerken van de index.

Voor de rest kan ik het alleen maar met je eens zijn


@tomatoman: je verhaal klopt natuurlijk wel gewoon als de index niet op de gemuteerde kolom ligt. Ik heb dus maar gedeeltelijk gelijk

[ Voor 31% gewijzigd door Annie op 14-11-2003 08:59 . Reden: kleine nuance ]

[VisionMaster]

Importeer je direct je file/script van CVS?
Gooi die effe lekker eerst op je local disk en smijt die dan pas door. Tenzij je dat al deed
Ik neem aan dat de file niet ZO enorm groot is (een paar MB maybe?) Dat moet toch absoluut geen probleem zijn.
Wat is je invoer methode?

code:

\. datafile.sql

Of heb je de externe import functie gebruikt.

[riezebosch]

Heb je ook al geprobeerd je Access DB naar XP te converteren (staat standaard op 2000 als je Access XP gebruikt). Heb keer artikel gelezen dat de verbeteringen hierin waren dat ie veel sneller was en veel groter kon zijn (de twee grootste minpunten bij voorgaande versies).

[bigtree]

Topic komt mij bekend voor...

[Verwijderd]

Zal wel mijn verknipte geest zijn dat ik een Oracle gek ben of zo maar kijk eens naar een goede oracle database...... Je kan een versie downloaden... je mag hem wel niet in een bedrijfs omgeving gebruiken zonder er voor te betalen maar als je het voor prive gebruikt ..... mmmmmmm "test doeleinden" of zo?

Kijk eens op http://otn.oracle.com

Misschien dat je er iets aan hebt.

justmental schreef op 13 november 2003 @ 22:58:
10 uur voor die mysql import vind ik veel, dat komt neer op 72 records per seconde, wat voor methode heb je daarvoor gebruikt?

Een individuele index op die velden lijkt me een goed idee.
Let wel op dat je door die PK waarschijnlijk al een index erbij krijgt.

[Nexopheus]

ok even een paar antwoorden geven

Ik heb eerst MySQL Front gebruikt om via ODBC de inmiddels gevulde Access
database te importeren in een mysql database.
Dit duurde enorm lang. [ODBC = schuldige ???]

Vervolgens heb ik met mysqlCC geprobeerd om via sql het cvs bestand
rechtstreeks te importeren. Dit gaat niet helemaal zoals ik wil.
hij importeerd maar 1 record

SQL:

LOAD DATA INFILE 'D:/Software/IP-COUNTRY-REGION-CITY-ISP.CSV' INTO TABLE tbllocation FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r' ;

Ik gebruik de windows versie van mysql.

Wat betreft de link naar het andere topic:
ik sla de ip-adressen al als een long op.
zal het topic nog even helemaal doorlezen.
lijkt verder idd hetzelfde probleem.

btw: De import van het csv in Access en MSSQL[msde] ging wel vrij vlot.

[ Voor 10% gewijzigd door Nexopheus op 14-11-2003 10:42 ]

[Suepahfly]

Ik heb zelf ook de IP-to-country cvs in een mysql DB gestopt met `load dat infile` via phpmyadmin.
Die deed er iets van 30 seconden over (+/- 50.000 records)

Dus dan zal 2 miljoen hooguit 2.5 minuut duren

[Nexopheus]

phpMyAdmin gaf me een CGI timeout error.
Zal wel even opzoeken hoe ik die timeout waarde wat kan verhogen, probeer ik het nog wel eens met phpMyAdmin.

[jan-marten]

Volgens mij was ODBC niet echt een snelle manier om MySQL te benadere.

[Dutch_guy]

Ook ik wil Access niet aanprijzen voor een dergelijke hoveelheid records, maar ik denk dat je toch iets fout doet. Misschien geen index gebruikt, o.i.d.

Ik heb hier bijvoorbeeld een Access database met een miljoen records, waar een select razendsnel is. Absoluut geen vertraging te merken.

[curry684]

jan-marten schreef op 14 november 2003 @ 10:53:
Volgens mij was ODBC niet echt een snelle manier om MySQL te benadere.

Kun je hierbij eens wat bewijsmateriaal opzoeken? Zo is het natuurlijk een losse kreet in de ruimte en ik persoonlijk ben hier bedrijfsmatig wel geinteresseerd in...

[Nexopheus]

ODBC is natuurlijk wel een extra laag, wanneer de ODBC Driver niet optimaal geschreven is kan hier mogelijk een bottleneck zitten. Hoe dit met de ODBC MySQL driver is weet ik niet.

[Suepahfly]

Als je de records in MySQL wilt opslaan dan zou ik zeker geen gebruik maken van ODBC.

Maar van de standaard MySQL client.

En dan met LOAD .. INFILE je data in een tabel stoppen.

En de timeout verhogen voor phpmyadmin doe je door

PHP:

set_time_limit(0); //oneindig

Ergens in de config.inc.php te zetten. Wel even in de source zoeken of er niet ergens anders een set_time_limit staat.

[Nexopheus]

Thx, zal ik meteen even proberen

[DaCoTa]

Ik heb eenzelfde probleem, maar dan met Oracle. Inlezen gaat ontzettend traag. De data gaat via JDBC/ODBC de database in, in een tabel waar een aantal indexen op liggen. Het inlezen gaat in het begin erg rap (75k records in de 1e minuut), maar daarna zakt het in elkaar als plumpudding (7 minuten voor de volgende 75k records). Na 500k records gaan er nog maar 2k records per minuut de database in, is de CPU bijna idle (10%) en de SCSI harddisk draait zich een gek in de rondte. Na 4 uur heb ik hem afgebroken, hij was toen op 1 miljoen records. Ik heb er iig nog geen oplossing voor.

Over het MySQL probleem , kun je de data inlezen zonder indexen (of alleen PK) en daarna in 1 keer de index op laten bouwen?

Toevoeging: MySQL is niet langzaam hoor, en ook niet met 2 miljoen records. Ik heb MySQL gebruikt met tabellen waar 10 miljoen records in zitten, draait rap, in een aantal gevallen zelf 50% sneller als Oracle. Ik gebruik trouwens wel InnoDB van MySQL3, niet de standaard tabellen. In MySQL4 is InnoDB dacht ik standaard geworden, maar dat weet ik niet.

[ Voor 20% gewijzigd door DaCoTa op 14-11-2003 11:15 ]

[Nexopheus]

dat ga ik nu maar eens proberen --> dus eerst importeren en daarna maar de index op de db aanbrengen.

edit : Inserted rows: 2510056 (Query took 27.8483 sec)

edit2: Zonder index en PK

code:

SELECT * 
FROM `tbllocation` 
WHERE 67297905 
BETWEEN ipFROM AND ipTO
LIMIT 0 , 30

4,6513 seconde.

Index aangemaakt als primary key op de velden ipFROM en ipTO en sql iets aangepast:

code:

SELECT * 
FROM `tbllocation` 
WHERE 67297905 
BETWEEN ipFROM AND ipTO
LIMIT 0 , 1

0,0171 seconde
SUPER dit

[ Voor 81% gewijzigd door Nexopheus op 14-11-2003 12:00 . Reden: WERKT!! ]

[VisionMaster]

Nexopheus schreef op 14 november 2003 @ 10:39:
hij importeerd maar 1 record

SQL:

LOAD DATA INFILE 'D:/Software/IP-COUNTRY-REGION-CITY-ISP.CSV' 
      INTO TABLE tbllocation FIELDS TERMINATED BY ',' 
      ENCLOSED BY '"' LINES TERMINATED BY '\r' ;

Waarom staat daar op het einde '\r' ?
Bij mijn weten is een '\r' in een C omgeving een carriage return. Dat betekent begin voor aan op de regel. Dit kan zich wel eens uiten in dezelfde regel. probeer eens voor de lol met een '\n' (newline).

edit: ok

[ Voor 37% gewijzigd door VisionMaster op 14-11-2003 12:03 ]

[Nexopheus]

moest idd een \n [newline] zijn.

[VisionMaster]

Nexopheus schreef op 14 november 2003 @ 12:02:
moest idd een \n [newline] zijn.

Wil het nu wel werken?

[Nexopheus]

JA

[Kippenijzer]

Ik vroeg me nog iets af dat hier deels aan gerelateerd is (lijkt me). MySQL zou sneller met INT's omgaan dan met andere waarden. Als je een ip nou opslaat als HEX, dan heb je maar 2 digit's per deel, is 8 digits in totaal, en het lijkt me dat met hex functies MySQL dan nog sneller met de data om zou kunnen springen, of zie ik dan iets ovet het hoofd? Lijkt mij wel handig omdat je dat geen problemen krijgt met ips waarvan een decimaal minder dan 3 digits heeft...

[Surehand]

DaCoTa schreef op 14 november 2003 @ 11:12:
Ik heb eenzelfde probleem, maar dan met Oracle. Inlezen gaat ontzettend traag. De data gaat via JDBC/ODBC de database in, in een tabel waar een aantal indexen op liggen. Het inlezen gaat in het begin erg rap (75k records in de 1e minuut), maar daarna zakt het in elkaar als plumpudding (7 minuten voor de volgende 75k records). Na 500k records gaan er nog maar 2k records per minuut de database in, is de CPU bijna idle (10%) en de SCSI harddisk draait zich een gek in de rondte. Na 4 uur heb ik hem afgebroken, hij was toen op 1 miljoen records. Ik heb er iig nog geen oplossing voor.

Heb je al geprobeerd om de indexen er af te halen voordat je de data gaat inlezen? (Uiteraard weer terugzetten als alles ingelezen is )

[DaCoTa]

Surehand schreef op 14 november 2003 @ 12:50:
[...]
Heb je al geprobeerd om de indexen er af te halen voordat je de data gaat inlezen? (Uiteraard weer terugzetten als alles ingelezen is )

Lol Helaas is dat bij mij niet mogelijk, omdat er meerdere processen op verschillende momenten van die tabel gebruik kunnen maken. Zijn aan het denken om ieder proces zijn een eigen tabel te geven en later de indexen aan te maken, maar is vrij lastig in te bouwen.

Wederom toevoeging: Je kan een IP toch binair coderen als een 32 bit int? Lijkt me verreweg het snelste. Heb hetzelfde een keer gedaan met postcode en Java's Integer.parseInt(zipcode, Character.MAX_RADIX); Werkt echt ideaal!

[ Voor 20% gewijzigd door DaCoTa op 14-11-2003 13:08 ]

[Verwijderd]

DaCoTa schreef op 14 november 2003 @ 11:12:
Ik heb eenzelfde probleem, maar dan met Oracle. Inlezen gaat ontzettend traag. De data gaat via JDBC/ODBC de database in....

kun je geen sqlloader gebruiken? (hoe wordt die data aangeleverd?)

[DaCoTa]

Verwijderd schreef op 14 november 2003 @ 13:29:
[...]
kun je geen sqlloader gebruiken? (hoe wordt die data aangeleverd?)

Helaas niet, er moet nogal stevige pre-processing gedaan worden voor het de tabel in gaat. O.a. huisnummer en postcode extractie en blobben van data.

Extra info: The Oracle (tm) Users' Co-operative FAQ - PLSQL Slowdown

[ Voor 17% gewijzigd door DaCoTa op 14-11-2003 16:11 ]

[Tomatoman]

DaCoTa schreef op 14 november 2003 @ 13:05:
[...]
Wederom toevoeging: Je kan een IP toch binair coderen als een 32 bit int? Lijkt me verreweg het snelste. Heb hetzelfde een keer gedaan met postcode en Java's Integer.parseInt(zipcode, Character.MAX_RADIX); Werkt echt ideaal!

Wat ben jij nog scherp op vrijdagmiddag . Wel even rekening houden met het feit dat alle IP-nummers dan als niet-negatieve getallen (unsigned integer) moeten worden geïnterpreteerd door de SQL statement, anders krijg je rare fouten.

[Nexopheus]

*kleine update*

De selects op de database blijven langzaam om het moment dat het nummer waarop ik selecteer groter wordt.

VB:

code:

SELECT countrySHORT
FROM tbllocation
WHERE 3564661875 
BETWEEN ipFROM AND ipTO
LIMIT 0 , 1

Showing rows 0 - 0 (1 total, Query took 3.9300 sec)

En

code:

SELECT countrySHORT
FROM tbllocation
WHERE 67297905 
BETWEEN ipFROM AND ipTO
LIMIT 0 , 1

Showing rows 0 - 0 (1 total, Query took 0.0175 sec)

Even de tabel structuur voor een compleet overzicht:

code:

Field  Type 
ipFROM  double
ipTO  double  
countrySHORT  char(2) 
countryLONG  varchar(64) 
region  varchar(128)                 
city  varchar(128)  
isp  varchar(255)

de indexen zijn als volgt:

code:

Keyname Type Cardinality          Field 
PRIMARY  PRIMARY  2510056      ipFROM  
                                                   ipTO  
iprange  INDEX  2510056           ipFROM  
                                                   ipTO

Iemand een idee hoe ik de gemiddelde tijd van de select lager en constanter kan krijgen ?

[Spider.007]

Nexopheus schreef op 14 november 2003 @ 10:39:
Vervolgens heb ik met mysqlCC geprobeerd om via sql het cvs bestand
rechtstreeks te importeren.

VisionMaster schreef op 13 november 2003 @ 23:37:
Importeer je direct je file/script van CVS?

Suepahfly schreef op 14 november 2003 @ 10:44:
Ik heb zelf ook de IP-to-country cvs in een mysql DB gestopt met `load dat infile` via phpmyadmin.

Letten we wel even op onze afkortingen Het door elkaar gebruiken van CVS en CSV kan tot verwarring leiden

CVS => Concurrent Versions System
CSV => Comma Separated Values

[VisionMaster]

Spider.007 schreef op 16 november 2003 @ 15:02:
[...]

Letten we wel even op onze afkortingen Het door elkaar gebruiken van CVS en CSV kan tot verwarring leiden

CVS => Concurrent Versions System
CSV => Comma Separated Values

00ps ... MyBlunder @ GoT
ok ...

[Nexopheus]

Is het zo dat MySQL Lineair zoekt ?
Het moet toch mogelijk zijn dat elke select evenlang (lees : kort) duurt ?? Ik wordt er beetje grrrrrrrrr van

[DaCoTa]

Nexopheus schreef op 17 november 2003 @ 16:28:
Is het zo dat MySQL Lineair zoekt ?
Het moet toch mogelijk zijn dat elke select evenlang (lees : kort) duurt ?? Ik wordt er beetje grrrrrrrrr van

Misschien kun je een EXPLAIN PLAIN doen, weet niet de exacte syntax van MySql hiervoor, maar dan zou je kunnen zien welke en of er van indexen gebruik wordt gemaakt. Ziet er idd naar uit dat MySql die index helemaal niet gebruik.

[Nexopheus]

dat klopt inderdaad, maar hoe kan dat ??? Of moet ik expliciet aangeven dat er een index gebruikt moet worden ?

code:

EXPLAIN SELECT * 
FROM `tbllocation` 
WHERE 1234587 
BETWEEN ipFROM AND ipTO

geeft

code:

table  type  possible_keys  key  key_len  ref  rows  Extra  
tbllocation ALL NULL NULL NULL NULL 2510056 Using where

[bigtree]

Nexopheus schreef op 17 november 2003 @ 18:50:
dat klopt inderdaad, maar hoe kan dat ???

Het zou moeten werken, gezien de manual:

An index is used for columns that you compare with the following operators: =, >, >=, <, <=, BETWEEN, and a LIKE with a non-wildcard prefix like 'something%'.

Op diezelfde pagina's staat echter in de comments een geval waarbij mysql een index negeert als er een impliciete typecast plaats moet vinden. Dat is bij jou ook het geval. Een double moet naar een integer geconverteerd worden. Maak eens heel rap een BIGINT van ipFROM en ipTO!!!

Of moet ik expliciet aangeven dat er een index gebruikt moet worden?

Dat zou je kunnen proberen. Lees het stukje over FORCE INDEX in de manual.

[Nexopheus]

Maakt niets uit

Uit de manual :

Any index that doesn't span all AND levels in the WHERE clause is not used to optimise the query. In other words: To be able to use an index, a prefix of the index must be used in every AND group.

Dit snap ik niet helemaal geloof ik. Bedoelen ze hiermee dat beide kolommen uit de AND clause in de index moeten zitten. of moet de naam de van de index als prefix gelden bij de namen van de kolommen in de AND clause ???

[bigtree]

Kijk even naar de voorbeelden die daar meteen onder staan. Het gaat om de volgorde waarin de kolommen in je index staan, die moeten overeenkomen met de volgorde waarin ze in je WHERE staan.

[Nexopheus]

die staan toch in de goede volgorde ?

code:

ALTER TABLE `tbllocation` ADD PRIMARY KEY (`ipFROM`,`ipTO`)

(ik neem aan dat een primary key ook een index is ?)

en dan

code:

EXPLAIN SELECT countrySHORT
FROM `tbllocation` 
WHERE 22344343 
BETWEEN ipFROM AND ipTO
LIMIT 0 , 1

geeft:

code:

table  type  possible_keys  key  key_len  ref  rows  Extra  
tbllocation ALL NULL NULL NULL NULL 2510056 Using where

Dat is dus niet goed, nu moet ie 2,5 miljoen records doorlopen.

Ik snap er niets meer van

[_Sunnyboy_]

Die index werkt niet als je between op deze manier gebruikt, maar kan je dit niet herschrijven als
IpFROM<=1234587 AND IpTO>=1234587.
Als je het op zo'n manier schrijft en een index hebt over eerst IpFROM en dan IpTO dan wordt ie wel gebruikt.

[ Voor 6% gewijzigd door _Sunnyboy_ op 17-11-2003 21:46 . Reden: oeps precies verkeerd om ]

[Nexopheus]

nee, want ik weet niet alle grenzen uit m'n hoofd natuurlijk

[_Sunnyboy_]

Die 1234587 kan je toch wel dynamisch genereren?

SELECT *
FROM `tbllocation`
WHERE ipFROM<=1234587 AND ipTO>=1234587

ipv

SELECT *
FROM `tbllocation`
WHERE 1234587
BETWEEN ipFROM AND ipTO

Volgens mij geven deze 2 queries hetzelfde resultaat maar de bovenste is wel met een index te optimaliseren en de onderste niet

[Nexopheus]

die grenzen zijn niet te berekenen, veranderen om de zoveel tijd. ZIjn ip ranges die zijn uitgegeven aan een isp voor een bepaalde stad in een bepaalde regio van een bepaald land

Ik kan dus niet zonder meer adv een ip adres bepalen wat de grenzen zijn waarbinnen ik m'n resultaten wil hebben.

daarom ook de between clause, die geeft me precies het goede resultaat.

In de manual van MySQL wordt wel een melding gemaakt van indexen bij een between.
zie : hier
Hoe bedoelen ze dat daar dan ? En waarop werkt mijn methode niet...?

[ Voor 3% gewijzigd door Nexopheus op 17-11-2003 22:46 ]

[justmental]

DaCoTa schreef op 14 november 2003 @ 16:03:
Helaas niet, er moet nogal stevige pre-processing gedaan worden voor het de tabel in gaat. O.a. huisnummer en postcode extractie en blobben van data.

Extra info: The Oracle (tm) Users' Co-operative FAQ - PLSQL Slowdown

Vaag verhaal, dat voorbeeld gaat 200x dezelfde keys inserten en dan moet sorteren helpen

Voor jouw probleem: als je veel inserts moet doen met complexe processing is het wellicht handig eens een bulk insert van een PL/SQL tabel (of losse arrays voor 8i) te proberen. Op deze manier beperk je de hoeveelheid context switches en geef je de db de mogelijkheid efficienter met zijn schijftoegang om te gaan.

Het snelle inserten van SQL*Loader kun je in PL/SQL overigens ook doen middels een APPEND hint waarbij je afhankelijk van de db configuratie ook nog de PARALLEL hint kunt gebruiken.

[bigtree]

Nexopheus schreef op 17 november 2003 @ 22:45:
die grenzen zijn niet te berekenen, veranderen om de zoveel tijd. ZIjn ip ranges die zijn uitgegeven aan een isp voor een bepaalde stad in een bepaalde regio van een bepaald land

daarom ook de between clause, die geeft me precies het goede resultaat.

code:

SELECT *
FROM `tbllocation` 
WHERE ipFROM <= 1234587 AND ipTO >= 1234587 
LIMIT 0 , 1

zou geen andere output moeten geven dan

code:

SELECT * 
FROM `tbllocation` 
WHERE 1234587 
BETWEEN ipFROM AND ipTO
LIMIT 0,1

Echter,

code:

EXPLAIN SELECT * 
FROM `tbllocation` 
WHERE 1234587 
BETWEEN ipFROM AND ipTO 
LIMIT 0 , 1

geeft:

code:

table  type  possible_keys  key  key_len  ref  rows  Extra  
tbllocation ALL NULL NULL NULL NULL 1999987 Using where

en

code:

EXPLAIN SELECT * 
FROM `tbllocation` 
WHERE ipFROM <= 1234587 AND ipTO >= 1234587
LIMIT 0 , 1

geeft:

code:

table  type  possible_keys  key  key_len  ref  rows  Extra  
tbllocation range PRIMARY PRIMARY 8 NULL 1282660 Using where

Ben je nu overtuigd dat je geen BETWEEN moet gebruiken?

Nog iets... je hebt kennelijk de link niet goed gelezen die ik in mijn eerste reply gaf. Daarin staat namelijk:

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.

Dit vervangt 100% je BETWEEN-oplossing. Het zou weliswaar voor kunnen komen dat je overlappende ip-ranges hebt, zodat de hierboven beschreven methode niet werkt, maar met een BETWEEN (...) LIMIT 0,1 hou je immers ook geen rekening met het feit dat je bij overlappende ip-ranges meerdere records terug zou kunnen krijgen. Oftewel, ga er nog even voor zitten.

[Nexopheus]

Ja ik heb die wel goed gelezen, maar ik was in de veronderstelling dat de between oplossing wel zou werken.

Zal het nu maar eens volgens die methode proberen, alhoewel ik nog niet helemaal zie waarom dat exact zou moeten kloppen

Ok dan,

code:

SELECT * 
FROM `tbllocation` 
WHERE 3564661875 <= ipTO
LIMIT 0 , 1

Toon Records 0 - 0 (1 totaal, Query duurde 0.0007 sec)

Ik is blij

[ Voor 96% gewijzigd door Nexopheus op 18-11-2003 09:33 . Reden: het werkt! ]

[_Sunnyboy_]

Nexopheus schreef op 17 november 2003 @ 22:45:
In de manual van MySQL wordt wel een melding gemaakt van indexen bij een between.
zie : hier
Hoe bedoelen ze dat daar dan ? En waarop werkt mijn methode niet...?

Ik begrijp dat het nu lukt?

MySQl kan wel indexen gebruiken op BETWEEN, maar dan in de zin van

code:

ipTO BETWEEN 10000 AND 20000

en dus niet op

code:

10000 BETWEEN ipTO AND ipFROM

[VisionMaster]

_Sunnyboy_ schreef op 18 november 2003 @ 11:20:
[...]


Ik begrijp dat het nu lukt?

MySQl kan wel indexen gebruiken op BETWEEN, maar dan in de zin van

code:

ipTO BETWEEN 10000 AND 20000

en dus niet op

code:

10000 BETWEEN ipTO AND ipFROM

Ben je Oracle gewend ofzo?
Wat jij aangeeft is niet wat er in de standaard van SQL zoal is gedefinieerd. Ik ken jou methode ook niet, maar het klinkt erg fancy omdat je nu je boolean expresie laat varen tussen twee variabelen op 1 record ipv dat je zoekt binnen welke records je ipTO op dit moment 'waar' doordat ze tussen je between waarden vallen.

[bigtree]

Nexopheus schreef op 18 november 2003 @ 09:07:

code:

SELECT * 
FROM `tbllocation` 
WHERE 3564661875 <= ipTO
LIMIT 0 , 1

Let wel even op, je vergeet iets. In woorden wil je 'het record met de kleinste ipTO die groter is of gelijk aan 3564661875' hebben. Dus:

code:

SELECT * 
FROM `tbllocation` 
WHERE 3564661875 <= ipTO
ORDER BY ipTO
LIMIT 0 , 1

[Nexopheus]

De records liggen allemaal op volgorde, maar expliciet aangeven is natuurlijk wel zo netjes. THx --> lekker alert

[bigtree]

Nexopheus schreef op 18 november 2003 @ 13:33:
De records liggen allemaal op volgorde

_{Dat is het idee van een index ja
Je zou in dit geval dus een aparte index op ipTo moeten hebben.}

[ Voor 15% gewijzigd door bigtree op 18-11-2003 13:38 ]

[Nexopheus]

dat is natuurlijk ook logisch. En die index ligt er dus ook

[ Voor 28% gewijzigd door Nexopheus op 18-11-2003 13:39 . Reden: index ipTO is aanwezig ]