[ bogdan.kecman @ 23.12.2015. 17:28 ] @
cesto (u ovom slucaju recimo je u pitanju maxmind tabela ali moze da bude bilo sta) imamo potrebu da uradimo upit sa 2 range uslova ... mysql je tu totalna truba :( moze da koristi samo jedan index i to je to ..

dakle ako imamo upit

select * from t1 where f1 > 10 and f2 <10;

mysql moze da koristi ili index po f1 da uradi range scan kroz index i onda nad rezultatom da radi table scan za f2 ili obrnuto, ne postoji mogucnost da iskoristi index i po f1 i po f2. takodje ne moze da se pomogne composite kljucem jer ako napravimo index (f1,f2) znamo da mysql ima limit da moze da radi samo "do prvog range scan-a" dakle da smo imali where f1 = 10 and f2 <10; onda bi index f1,f2 bio idealan, on bi iskoristio prvi deo indexa za eq i drugi deo indexa za range ali u slucaju kada su oba range dalje nema mogucnost da prodje ...

ovaj upit na nekoj maxmind tabeli sa nekih 11M slogova traje na nekoj tamo relativno ok masini nekih 12 sekundi .. kako god redizajnirali tabelu to mu dodje to (original maxmind tabela taj upit trosi i preko 20sec ali brisanjem kolona koje nisu potrebne, malim skracivanjem tabele, dodavanjem kompozitnog indexa cisto da ne cita sadrzaj iz tabele vec iz indexa koji je vec iskesiran u ramu i slicno spusteno je na 12sec) .. posto imamo ovde situaciju da je ono "10" uvek isto i za F1 i za F2 (u originalu oricamo o fromIP i toIP vrednostima, dakle trazimo slog u tabeli koji obuhvata neki ip koji mi imamo da bi odatle izvukli lokaciju za taj ip) rekoh aj da probamo da iskoristimo spatialne indexe ... pravimo novu tabelu (koja ce finalno zameniti originalnu)

Code (sql):

CREATE TABLE `bogimm` (
  `latitude` DECIMAL(10,5) DEFAULT NULL,
  `longitude` DECIMAL(10,5) DEFAULT NULL,
  `geo_ip` geometry NOT NULL,
  SPATIAL KEY `i_geoip` (`geo_ip`)
) ;
 


i onda je punimo direkt iz maxmind tabele

Code (sql):

INSERT INTO bogimm
  SELECT  
    latitude,
    longitude,
    GeomFromText(CONCAT('POLYGON((',ipFrom,' 0, ',ipTo,' 0, ',ipTo,' 2, ',ipFrom,' 2,',ipFrom,' 0))'))  
  FROM mm ;
 


sta smo uradili, kreirali smo umesto fromIP i endIP jedan geo_ip atribut koji je tipa "geometrija" i nad njim digli spatial index. onda smo u taj atribut upisali poligon sirine od fromIP do endIP visine 2 (cisto da ima neku povrsinu) i sada kada trazimo nas IP sve sto radimo je pretvorimo ga u tacku i pitamo da li se ta tacka nalazi u poligonu koristeci spatialni index ... i upit koji je trajao 12 sekundi

Code (sql):

mysql> SELECT SQL_NO_CACHE latitude, longitude FROM bogimm WHERE contains(geo_ip, GeomFromText('POINT(789049030 1)'));

| latitude | longitude |
| 45.34330 | -75.82650 |

1 ROW IN SET (0.00 sec)
 


ubrzanje od "kolko oces" :D zauzece servera "nista" :D

nije bas univerzalno resenje za univerzalno mnogo stvari ali zanimljiv primer
- kako se koriste spatialni indexi
- kako se problem resava "thinking outside the box"
- kako neke relativno jednostavne stvari mysql ne moze da odradi "out of the box" vec mora da se izvode vratolomije (pgsql originalni upit izvrsava mnogo brze)