Optimizacion de consultas mySQL

[daemonz]

Buenas, he creado un sistema ke guarda las conversaciones y acciones de canales de IRC, y otro ke genera estadisticas en tiempo real, podeis verlo en accion aqui: http://www.thedae.net/index.php?s=stats&canal=hardlimit

Los logs los guardo en mysql, y de momento la BD es pekeña, pero temo ke cuando se haga mas grande de unas pocas miles de filas, la poca optimizacion ke tiene el codigo de las estadisticas haga ke los tiempos de carga sean enormes.

La forma de la tabla ke guarda los logs es la siguiente:

mysql> describe log;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| tipo  | text |      |     |         |       |
| fecha | int  |      |     |         |       |
| canal | text |      |     |         |       |
| nick  | text |      |     |         |       |
| host  | text | YES  |     | NULL    |       |
| texto | text |      |     |         |       |
+-------+------+------+-----+---------+-------+
6 rows in set (0.14 sec)

y el codigo (PHP) para ver las lineas ke ha escrito cada uno:

$chan=$_GET[canal];
$stats=array();
$nicks=mysql_query("select distinct nick from log where tipo='privmsg' and canal='$chan'");
while ($ws=mysql_fetch_array($nicks)) {
    $query=mysql_query("select count(*) from log where tipo='privmsg' and canal='$chan' and nick='$ws[nick]'");
    $tarray=mysql_fetch_array($query);
    addArray($stats,$tarray[0],$ws[nick]);
    mysql_free_result($query);
}

de tal forma ke en el array $stats kedan guardados todos los nicks con sus respectivas lineas escritas.

Estaria muy agradecido si algun experto en SQL pudiera echarme una mano para optimizar las consultas, y en definitiva, el codigo ke he usado, puesto ke la carga cada vez es mayor.

Saludos y gracias!

[overmex]

no le veo ningun problema creo que mas no se podria optimizar el codigo, y ademas tiene una respuesta de 250 ms bastante desente (buena) de cuantos registros estas manejando hoy

[daemonz]

Gracias por tu respuesta overmex, pensaba ke ya nadie responderia :susto:

Los datos ke manejo empiezan a ser preocupantemente grandes, segun phpMyAdmin:

Data 2,529 KB
Index 1,024 Bytes
Total 2,530 KB

Format dynamic
Rows 31,605
Row length ø 81
Row size ø 82 Bytes
Creation Jan 02, 2004 at 05:22 PM
Last update Jan 05, 2004 at 08:35 PM

Son casi 32k de filas en unos 3 dias, en poco tiempo se me disparará, por eso necesito usar queries de mysql distintos o esos mismos pero optimizados.

Gracias de nuevo y saludos!

[overmex]

realmete es muy pequeña

en que equipo estan corriendo ?
que so tienes ?
manejas extends ?
que motor de base tienes?

[daemonz]

No es mi makina local, es un servidor de por ahi, hasta donde tengo entendido es un celeron 2,4 512mb de ram con redhat 7.2. Las otras dos preguntas no te las se responder, si me explikas un poco de ke van, pues kiza xD

[jfaba]

Hola,

Supongo que habrás hecho lo de crear un índice con los campos (tipo, canal, nick) en este orden, ¿no?

Salu2!

[marcalj]

Hola buenas, a ver si te puedo ayudar…

Te propongo un lijero cambio a ver si resulta util. El funcionamiento, tengo entendido k es este:

Sistema actual

1. [AUTO una vez por mensaje] Alguien pasa cada linia de conversacion i te los guarda en la DB.
2. [MANUAL todo por consulta web] Abres la pagina, genera los stats, los muestras.

Sistema propuesto, tendrias que crear otra tabla "stats" con la informacion referente a cada usuario:

p.ej: nick, mensajes_canal_X, mensajes_canal_Y, mensajes_canal_Z,….. (ejemplo fatal, solo para k veas la idea).

Entonces el procedimiento del sistema seria:

1. [AUTO una vez por mensaje] Alguien pasa cada linia de conversacion i te los guarda en la DB.
   2) [AUTO una vez por mensaje] Actualizacion de la tabla stats.
   3) [MANUAL todo por consulta web] Abres la pagina, muestras stats. [NO CALCULAS NADA]

Entonces el tiempo de ejecucion se reduciria considerablemente invariable en el tiempo.

Lo unico que no solucionaras es la capacidad de la tabla log. K sinceramente no creo que sea necesario guardar toooodas las frases, si kieres podrias guardar en otra tabla el topALGO de frases, pero guardarlas todas es muy a saco, engordas demasiado la db.

O sea que el bot tendria k añadir la info a la tabla log i actualizar la tabla stat. Y yasta

Espero k te haya servido de algo :sisi:

Salu2!

[daemonz]

Si, ya pense en ese sistema al principio y la verdad eske es una gran idea, un sencillo update de la DB en el nick ke acaba de hablar. Pronto la traducire a ese sistema a ver ke tal.

Lo de guardar el log… eske no se si habras visto ke en la misma web salen las 50 ultimas lineas de los canales, y evidentemente tengo ke almacenarlas. Ademas siempre viene bien tenerlo ahi, total, con ese sistema no se consulta, solo se añaden nuevas lineas

Gracias por vuestra ayuda a todos, ya os comentare como va :sisi:

Saludos!