Valsts noteikšana pēc ip adreses, optimizēta pieeja.

[Maris-S]

Jā, to pamanīju, bet pirms tam mēģināju mainīt un izmantot fromaddr sakārtošanai, bet līdz galam nesagaidīju, arī tagad tāds vaicājums izpildās daudz ilgāk, pagaidām nav izpildījies un nezinu kāds būs kopējais laiks. Izskatās ka labāk sakārtot tieši pēc toaddr, protams ja izmanto šo pieeju.

 

select sql_no_cache
a.ip,
(select `name` from iptocountry where fromaddr <= inet_aton(a.ip) order by fromaddr desc limit 1)
from
ip_addresses a

[codez]

cik ieraksti ir ip_addresses tabulā?

[codez]

problēma šķiet nav pašā kverijā, jo šāds kverijs vienai ip adresei izpildās zem 1ms.

select `name` from iptocountry where fromaddr <= inet_aton('123.123.123.123') order by fromaddr desc limit 1

Problēma varētu būt drīzāk tajā, kad šis kverijs ir kā subkverijs, mysql viņu izpilda kaut kā savādāk.

[codez]

Lai nebūtu šis kverijs jāizsauc kā subkverijs, var pamēģināt to ielikt funkcijā:

 

CREATE DEFINER=`root`@`localhost` FUNCTION `getcountry`(addr VARCHAR(15)) RETURNS varchar(255) CHARSET utf8
   NO SQL
   DETERMINISTIC
BEGIN
       RETURN (SELECT cname FROM ips WHERE fromaddr<=inet_aton(addr) ORDER BY fromaddr DESC LIMIT 1);
END

 

un tad:

 

SELECT sql_no_cache
 getcountry(ip)
FROM
 addresses

[Maris-S]

Codez, ip adrešu tabulu sataisīju no Marrtins saraksta, ko viņš iedeva šeit: http://php.lv/f/topic/12005-kadus-darba-efektivitates-tulus-izmantojat/page__view__findpost__p__96078 . Tabulā mazliet vairāk kā 18000 ieraksti.

 

Par funkciju nezinu, iespējams Tev taisnība par subquery. Pagaidām nemēģināju taisīt funkciju, būs jāpamēģina, to tagad gan nesanāks pārbaudīt, būs pirmdien jāpamēģina.

Edited November 26, 2010 by Maris-S

[Maris-S]

Sanāca izmēģināt arī ar funkciju. Nu cepuri nost tādai pieejai. Nezinu kāpēc ar apakšvaicājumu nestrādāja kā vajag, bet ar izveidotu funkciju sanāca pat pārspēt bitu (15 bitu) pieeju. Vaicājums izpildījās 0,887 sekundes, ar 15 bitu tabulu izpildījās 1,124 sekundes. Mēģināju līdzīgā veidā pārveidot bitu vaicājumu, ieliekot funkcijā, sanāca lēnāk. Vienīgais veids kā sanāca ar bitiem uzlabot Codez pieejas ātrumu ir taisīt bitu tabulu ar mazāk bitiem. Starp citu, te jāpiezīmē ka uzsākot šo tēmu rakstīju ko vairāk bitu to ātrāk strādās un būs lielāka tabulu, tur es kļūdījos būtībā ir pretēji, ko mazāk bitu to ātrāk strādā un lielākas tabulas.

 

Tātad, lai mazliet apdzītu Codez pieejas izpildes laiku pietika ar 14 bitiem, sataisīju arī 12 bitu tabulu, mēģināju arī 10 bitus, bet tā arī nesataisīju, pārāk ilgi jāgaida.

 

Tālāk informācija par ātrumiem un tabulu izmēriem.

 

15 biti:
  laiks          - 1,124 sekundes,
  datu garums    - 11868152
  indeksa garums - 2041856

14 biti:
  laiks          - 0,729 sekundes,
  datu garums    - 17184196
  indeksa garums - 2962432

12 biti:
  laiks          - 0,414 sekundes,
  datu garums    - 50238516
  indeksa garums - 8615936

 

Domāju parastai mājas lapai, kurā ne tik bieži jānosaka valsts pēc ip, mierīgi var izmantot Codez pieeju, strādā ļoti ātri, ja nepieciešams mazliet ātrāk tad jāģenerē bitu tabulas, domāju jāsāk ar 12 bitiem, citādi nav īpaši ātrāk kā Codez pieejai. Nezinu par cik 10 bitu tabula būs ātrāka, man liekās sanāk diezgan maz intervālu, kas nav sadalīti 12 bitiem, tas netika izmēģināts. Protams ja vajadzīga ļoti bieža valstu noteikšana, tad labākais ir Marrtins piedāvātais risinājums, vienīgi php paplašinājuma vietā būtu labāk veidot dll/so MySqlam, lai funkciju varētu izmantot pašā datubāzē, ne tikai php pusē.

 

Man vienīgi tagad radās jautājums. Kāpēc funkcija tik labi uzlaboja apakšvaicājuma pieeju?

[codez]

domāju, ka kverijam uz ips tabulu atrodoties apakškverijā, mysql nemāk izmantot kādu no optimizācijām, vai nu index-u vai nu LIMIT un tādēļ taisa pilnu tabulas skanēšanu. Ieliekot funkcijā, viņš šo kveriju uzskata par pilnīgi neatkarīgu un izmanto visas optimizācijas, tāpat kad kverijs tiek izpildīts viens pats.

Pati funkcija nedod nekādu paātrinājumu, drīzāk palēlina.

Tas, ko tu uzbūvē no bitiem, būtībā ir hash tabulas imitācija.

Vēl iespējams varētu palielināt ātrumu, ja tu izmantotu abas pieeja kopā un izpildītu funkcijā šādu kveriju:

 

SELECT cname FROM ips WHERE bit=inet_aton(addt)>>15 and fromaddr<=inet_aton(addr) ORDER BY fromaddr DESC LIMIT 1

 

un indeksu uzliktu kā bit, fromaddr

[Maris-S]

Jā, varētu būt ka neoptimizē, vēl variants ka izmantojot apakšvaicājumu viņš veido kaut kādu joinu, kas varbūt palēnina visu, bet iespējams kļūdos. Apvienot abas pieejas arī mēģināju, bet sanāca lēnāk, tādā gadījumā arī jātaisa vai nu apakšselekts vai arī funkcija, tā, lai varētu izmantot limit nevis visam vaicājumam, bet tieši valsts noteikšanai. Man liekās ka tur salīdzinoši maz atliek ierakstu ko pārbaudīt pēc atlasīšanas pēc bitiem, tāpēc arī nekas ātrāks nesanāk.