Optimizando o CMD BETWEEN no SQL

fLaSh_CF

fLaSh_CF

Banido
Boas;

Estou com uma duvida em relação ao comando BETWEEN..
Preciso de obter o Tops Contries de uma BD em SQLite..
Fiz o query da seguinte forma e functiona.. (CODE)
O problema é que está um pouco lento, precisava de optimizar o query..

Já experimentei desta forma, mas não funciona.. (a condição..)
WHERE 1401213123 or 1384067172 or ..... or .... or ... etc...
BETWEEN blocks.start_ip_num AND blocks.end_ip_num
Clicar para expandir...

Se alguem poder ajudar ..
Código: 
SELECT DISTINCT country, COUNT(country) AS TOTAL 
FROM blocks JOIN locations ON blocks.loc_id = locations.loc_id 
WHERE (1486149509 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (2537819614 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (701684425 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (2130706433 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1495497275 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1437975106 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (3575017176 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1356762882 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1310809035 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1436948856 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1265441955 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1440990995 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1404512164 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1413143436 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1217187988 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1491831804 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (3362243165 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1364484315 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1351251084 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1446856464 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1414245739 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1332754719 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1334197131 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1333236289 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1393808330 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1295438787 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1502508649 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1318873170 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1545949891 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1314528380 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1474280303 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (2439230330 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1314174166 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1333070193 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (3273849874 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1589488396 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (3582727755 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1415218381 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1478288548 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1364503911 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1414262933 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1332905661 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1333011343 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1330877193 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1435199056 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1472464343 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1333070716 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1460131478 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1549004602 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (2197024396 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1144987038 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1385839702 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (3653734094 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1332938430 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1332931591 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1449725108 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
OR (1385922226 BETWEEN blocks.start_ip_num AND blocks.end_ip_num) 
GROUP BY country 
ORDER BY TOTAL
Assim funciona, mas demora cerca de 1 minuto o query ..

A ideia é apresentar os resultados da seguinte forma:
-----------------------
País -------------Total
-----------------------
Portugal -------- 120
França --------- 90
USA ------------ 50
Espanha -------- 60
etc...............------
-----------------------
Clicar para expandir...

Notas:
*A base de dados tem cerca de 3,2 milhões de IP ranges, eu penso que se o query for bem optimizado fica muito mais rapido..
*Um query unico demora cerca de 0.5~2 segundos..
Ex:
Código: 
SELECT SELECT country, region, city, postal_code, latitude, longitude, dma_code, area_code 
FROM blocks JOIN locations ON blocks.loc_id = locations.loc_id 
WHERE 1401213123 BETWEEN blocks.start_ip_num AND blocks.end_ip_num
LIMIT 1

Table Schema (2 tabelas):
TABLE blocks (
start_ip_num bigint not null,
end_ip_num bigint not null,
loc_id integer not null)

TABLE locations (
loc_id integer not null primary key,
country varchar(2) not null,
region varchar(2),
city varchar(255),
postal_code varchar(10),
latitude double,
longitude double,
dma_code integer,
area_code integer)
Clicar para expandir...

Compr.
 
Última edição:
podes começar por tirar o distinct (SELECT DISTINCT country, COUNT(country) AS TOTAL ) visto estares a agrupar por país não tem sentido estares a colocar essa instrução.

depois coloca um indice no star_ip_num e no end_ip_num.
 
EU não percebo de SQLite mas em Oracle provavelmente ficaria mais rápido usando partições por blocos de ips com indices locais. Se der experimenta.
Outra coisa que reparei é que não tens indices nas tabelas. O SQLite não suporta ou esqueceste-te?
 
KiKas disse:
podes começar por tirar o distinct (SELECT DISTINCT country, COUNT(country) AS TOTAL ) visto estares a agrupar por país não tem sentido estares a colocar essa instrução.

depois coloca um indice no star_ip_num e no end_ip_num.
Clicar para expandir...

Obrigado por os comentarios..

Retirei o DISTINCT mas mesmo assim o tempo de execução continua o mesmo (40 ~ 60 segundos)..
Quanto ao indice , podes ser mais concreto, que tipo de indice (ordenar) ?
Criei um indice de ordenação mas também não reparei em nenhuma melhoria ..

SQLite suporta indexes, tiggers e view tal como qualquer outra linguagem SQL :)

Continuo com o mesmo problema, mais alguma sugestão ?
 
Às vezes há coisas que são melhor feitas fora do SQL.

1º Primeiro faria a junção:
Código: 
SELECT country, start_ip, end_ip
FROM blocks JOIN locations ON blocks.loc_id = locations.loc_id;
para ter a lista:

Código: 
País           Start_ip End_ip
Portual          1           10
Portugal       11           20
Italia           20           30
2º Depois com uma linguagem de programação que estás a usar iteras o resultado.

Código: 
for i in lista_de_ips
loop
    for j in resultado_da_query
    loop
    
        if i between j.start_ip and j.end_ip
        then
            countries[j.country] ++;
            break;
        end if;
    end loop;
end loop;
3º O problema do passo 2 é que tens uma complexidade linear, se no passo 1 a lista já vier ordenada por start_ip, já consgues uma complexidade O(log n). Podes começar a avaliar pelo meio da lista de resultado, se o ip for menor que o start_ip procuras no meio do meio, etc

estilo uma procura binaria


O único problema é que no passo 1 estás a puxar tudo para memória :D
 
Última edição:
Inicie sessão ou registe-se para poder participar.
Back
Topo