Como Obtimizar esta Consulta?

[abigor66]

Muy buenos días Gente del Foro y Felices Fiestas!!
estoy haciendo una consulta en mi BD donde la idea es saber de que paises me visitan los usuarios, el detalle es que el dato que necesito es la lista de paises y cuantos usuarios unicos me han visitado (utilizo l aip del visitante para definir un usuario único)

bueno, tengo una BD con los rangos de ip de todos los paises son 102.339 registros y tengo otra tabla con las ip de los usuarios que me visitan (hay ip repetidas pues son usuarios recurrentes)

he escrito el siguiente código para hacer la consulta:

Código PHP:

SELECT T1.ip_publica AS ip, T2.*, T2.country_name AS pais, COUNT(T2.country_name) AS total FROM (SELECT * FROM stat_user GROUP BY ip_publica) AS T1 LEFT JOIN stat_country AS T2 ON INET_ATON(T1.ip_publica)>=T2.IP_FROM AND INET_ATON(T1.ip_publica)<=T2.IP_TO GROUP BY pais ORDER BY total DESC LIMIT 0,10
 

el resultado es el esperado, pero se demora +/- 30 segundos en arrojar el resultado.

Alguien conoce una mejor forma de hacer esta consulta?

Muchas gracias por su tiempo y colaboración.

[huesos52]

No utilices el *.

solo llama los campos que requieras. En tu caso ip_publica.

Prueba así y dinos si mejora

Código SQL:

SELECT T1.ip_publica AS ip, T2.*, T2.country_name AS pais, COUNT(T2.country_name) AS total FROM (SELECT ip_publica FROM stat_user GROUP BY ip_publica) AS T1 LEFT JOIN stat_country AS T2 ON INET_ATON(T1.ip_publica)>=T2.IP_FROM AND INET_ATON(T1.ip_publica)<=T2.IP_TO GROUP BY pais ORDER BY total DESC LIMIT 0,10
 

o así

Código SQL:

SELECT T1.ip_publica AS ip, T2.*, T2.country_name AS pais, COUNT(T2.country_name) AS total FROM (SELECT DISTINCT ip_publica FROM stat_user) AS T1 LEFT JOIN stat_country AS T2 ON INET_ATON(T1.ip_publica)>=T2.IP_FROM AND INET_ATON(T1.ip_publica)<=T2.IP_TO GROUP BY pais ORDER BY total DESC LIMIT 0,10
 

[abigor66]

Hola huesos52 gracias por tu interes, te comento: mi código se ejecuta en aprox. 28 seg, la primera opción que escribes tambien se ejecuta en aprox 28 seg y la última opción en aprox. 29 seg.

he de comentar que esto lo estoy ejecutando de manera local.

alguna otra idea?

[gnzsoloyo]

Dos de las cláusulas que hay que evitar en lo posible en estos casos son GROUP BY y ORDER BY, ya que son las mayores consumidoras de recursos.
En tu caso la exterior no se puede evitar, pero si la de la subconsulta, ya que carece completamente de utilidad, cuando ya existe una más eficiente.
Esto sería una posibilidad:

Código MySQL:

SELECT
  T1.ip_publica AS ip,
  T2.*,
  T2.country_name pais,
  COUNT(T2.country_name) AS total
FROM
  (SELECT DISTINCT ip_publica FROM stat_user) T1 LEFT JOIN stat_country T2
    ON INET_ATON(T1.ip_publica)>=T2.IP_FROM AND INET_ATON(T1.ip_publica)<=T2.IP_TO
  GROUP BY pais
  ORDER BY total DESC LIMIT 0,10
 

Aún así, dos de las columnas pedidas son virtualmente inutiles, si estás agrupando por valores que ocultarán datos o los simplificarán. Esto es, T1.ip_publica y T2.*, serán absorbidos por el agrupamiento. En todo caso habría que ver si realmente necesitas todos los campos de la tabla stat_country (eso no se puede apreciar, porque no se sabe qué campos tiene).
Tal vez esto sea suficiente:

Código MySQL:

SELECT
  T2.country_name pais,
  COUNT(T2.country_name) total
FROM
  (SELECT DISTINCT ip_publica FROM stat_user) T1 LEFT JOIN stat_country T2
    ON INET_ATON(T1.ip_publica)>=T2.IP_FROM AND INET_ATON(T1.ip_publica)<=T2.IP_TO
  GROUP BY pais
  ORDER BY total DESC LIMIT 0,10;
 

Lo que yo haría es esto, para obtener sólo la info de los países que forman parte del resultado:

Código MySQL:

SELECT T4.*, total
FROM (SELECT
  T2.country_name pais,
  COUNT(T2.country_name) total
FROM
  (SELECT DISTINCT ip_publica FROM stat_user) T1 LEFT JOIN (SELECT IP_FROM, IP_TO FROM stat_country) T2
    ON INET_ATON(T1.ip_publica)>=T2.IP_FROM AND INET_ATON(T1.ip_publica)<=T2.IP_TO
  GROUP BY pais)) T4
  INNER JOIN stat_country S ON pais = D.country_name
ORDER BY total DESC
LIMIT 0,10
 

[abigor66]

gnzsoloyo gracias por tu respuesta, los dos primeros códigos demoran lo mismo que el que yo tengo y el último me da error, he corregido el parentesis que tiene de más en T4 y el error al llamar a la tabla renombrada S, pero aún así dá error.

de momento y en base a tu explicación del uso del GROUP BY, lo he modificado en t2 y en su lugar he utilizado DISTINCT, sin embargo, el tiempo de ejecucuón sigue siendo el mismo aprox. 28seg

Muchas Gracias y espero me sigan ayudando.

[gnzsoloyo]

Veamos un detalle de performance:
Cuánto tarda la lectura de cada tabla por separado, cuando le haces

Código MySQL:

SELECT * FROM tabla;
 

[jurena]

perdón,
he repetido el mensaje. Mira en el siguiente.

[jurena]

SELECT c.PAIS, count( * ) totpaises FROM tablaIPs INNER JOIN (SELECT u.numero FROM tablausuariosonline u GROUP BY u.numero)t1 ON t1.numero >= tablaIPs.start AND t1.numero <= tablaIPs.end INNER JOIN tablapaises c ON c.name = tablaIPs.cn GROUP BY tablaIPs.cn ORDER BY totpaises DESC

Esta consulta la tengo funcionando y aunque es algo lenta, no lo es tanto. Es importante que tengas indexados los campos que enlazas. Me refiero a c.numero, tablaIPs.start y tablaIPs.end. Observa que añado otro INNER JOIN con una tablapaises donde tengo los nombres de los países en español. Esta es la idea. Lo que no entiendo en tu consulta es el LEFT JOIN, que seguro que la ralentiza demasiado y no comprendo su función.
Intenta adaptar la consulta incluyendo los nombres de tus campos.
Te quito el último INNER JOIN

Código SQL:

SELECT tablaIPs.country, COUNT( * ) totpaises FROM tablaIPs
 INNER JOIN (SELECT u.numero FROM tablausuariosonline u GROUP BY u.numero)t1
 ON t1.numero >= tablaIPs.START AND t1.numero <= tablaIPs.END
 GROUP BY tablaIPs.cn ORDER BY totpaises DESC
 

No olvides indexar los start y end.

[abigor66]

jurena gracias por tu ayuda, efectivamente tu código correo muy bien, la consulta se redujo a aprox. 6 seg

gnzsoloyo gracias por tu ayuda.

[jurena]

abigor66,
creo que te estoy llevando a error porque no te he aclarado todo lo que hice. A la ip de usuario que visita mi página le aplico INET_ATON() antes de guardarla en un campo al que llamo numero en la tabla que yo tengo de useronline. En la tabla donde aparecen todas las IPs por países existe un campo IP_start y otro IP_end; yo creé dos campos, uno start y otro end, ambos numéricos, que eran el resultado de aplicar INET_ATON() a IP_start e IP_end, respectivamente. Tras crear esos dos campos en la tabla donde tienes todas las IPs un par de updates del tipo:
UPDATE tabla SET start= INET_ATON(IP_start)
UPDATE tabla SET end = INET_ATON(IP_end)
Naturlamente indexé luego start y end.
Lo que comparo es si ese numero, es decir, el resultado de aplicar INET_ATON() a la IP del usuario que entra, con la aplicación de INET_ATON a IP_start e IP_end, es decir, con start y end debidamente indexados.
La búsqueda me tarda 0,33 segundos, no 6 segundos, y eso que, como te dije, yo hago un cruce más con otra tabla. Mira a ver si puedes hacer lo mismo. Si estás buscando por IPs tal y como se representan, el resultado será incorrecto.