Agrupar y calcular datos en una consulta con varias tablas

[lbmdw]

Hola,

Soy nuevo en el foro, os he leído mucho pero jamás había escrito. Llevo mucho tiempo usando MySQL, pero ahora me enfrento a una situación en la que me gustaría que alguien me orientara un poco por donde tirar.

El caso es que quiero hacer algo que posiblemente cargue mucho la Base de Datos y necesito alguna sugerencia.

Imaginemos que tengo una tabla de usuarios y tres tablas de acciones:

• Usuarios: id, nombre, puntuacion
• Accion1: id, id_usuario, datos, timestamp
• Accion2: id, id_usuario, datos, timestamp
• Accion3: id, id_usuario, datos, timestamp

Me gustaría que cada X tiempo, cada 5 minutos o cada 10 minutos, la puntuación de los usuarios se actualice a través de un cron con un script PHP, con una fórmula basada en el número de acciones realizadas en distintos periodos de tiempo. Lo simplifico para que me entendáis. Imaginaros que quiero que la puntuación sea una acumulación de puntos por la cantidad de acciones realizadas Accion1, Accion2 y Acción3:

• 2, 4 y 6 puntos respectivamente por cada acción relizada en las últimas 24 horas
• 1, 2 y 3 puntos respectivamente por cada acción realizada hace entre hace 24 y hace 48 horas

Si por ejemplo cada 5 minutos debo calcular la puntuación de todos los usuarios.

¿Cuál es la mejor manera de hacerlo?

Se me ocurren varias, pero como el número de usuarios y el número de acciones puede crecer muchísimo quiero hacer las cosas bien desde el principio para optimizar este proceso al máximo.

Mis opciones siempre pasan por contabilizar registros y luego tratarlos con PHP para hacer chorrocientos UPDATES usuario a usuario.

No se si debo intentar hacer algún JOIN, buscar hacer consultas con algún CASE o IF, o si las vistas me servirán de algo (nunca las he utilizado).

Toda ayuda será bien recibida, ya sea con sugerencias de consultas o con caminos hacia los que orientarme e investigar la solución, pero seguro que alguno de vosotros me puede echar un cable y ayudarme para al menos dirigirme hacia el buen camino.

Muchas gracias por adelantado.

[walterdevel]

Lo mejor sería que pudieras actualizar el puntaje en cada acción ( en el momento que ocurre la acción ). No creo que sea recomendable que tengas un cron haciendo eso cada 5 o 10 minutos, a no ser que tengas y vayas a tener en el futuro pocos usuarios.

Algo que no entiendo, tal vez tengas tus razones, es que uses 3 tablas para las acciones cuando seguramente 1 sola te alcance. El tema es que con 3 tablas, si vas a calcular con un cron, es que vas a tener muchas más consultas o un JOIN inmenso que va a matar tu servidor.

Si vas a usar un cron, yo te aconsejo crear indices para la unión de tablas, que en este caso sería en id_usuario de las tablas de acciones.

[lbmdw]

Iniciado por walterdevel

Lo mejor sería que pudieras actualizar el puntaje en cada acción ( en el momento que ocurre la acción ). No creo que sea recomendable que tengas un cron haciendo eso cada 5 o 10 minutos, a no ser que tengas y vayas a tener en el futuro pocos usuarios.

Algo que no entiendo, tal vez tengas tus razones, es que uses 3 tablas para las acciones cuando seguramente 1 sola te alcance. El tema es que con 3 tablas, si vas a calcular con un cron, es que vas a tener muchas más consultas o un JOIN inmenso que va a matar tu servidor.

Si vas a usar un cron, yo te aconsejo crear indices para la unión de tablas, que en este caso sería en id_usuario de las tablas de acciones.

Gracias por tu respuesta walterdevel.

Seguramente aprovecharé a actualizar la puntuación en cada acción a fin de facilitar la actualización más rápidamente en los usuarios activos, pero es necesario por las características de la aplicación que cada 5 o 10 minutos las puntuaciones se actualicen, pues según pasa el tiempo, las puntuaciones se degradan, ya que las acciones más modernas puntúan más que las más antiguas.

En el ejemplo que cuento, hablo de dos rangos de tiempo para simplificar, las primeras 24h y las siguientes 24h, pero en la aplicación real existen varios rangos de tiempo desde los primeros 15 minutos, las siguientes 3 horas, las siguientes 8 horas....

También en el ejemplo simplifiqué las tablas de las acciones pues además de estos cambios que digo tienen otros, pero analizando tu respuesta, he simplificado las 3 tablas en 1 sóla realizando un poco más de programación y de esta forma simplifico bastante las consultas que tendré que hacer.

Ahora bien, con una tabla de acciones había pensado obtener con una consulta la relación ID y puntuación y luego recorrer el resultado para lanzar multiples updates por cada id de forma individual.

Asumiendo que la tabla acciones la hacemos en una sola con un nuevo campo tipo_accion con valor 1, 2 y 3 según el tipo de acción:

Acciones: id, id_usuario, datos, timestamp, tipo_accion

Sería demasiado para ejecutar algo similar a:

Código MySQL:

SELECT id_usuario,
  -> SUM(IF ((timestamp BETWEEN NOW() AND NOW()+86400) AND tipo_accion = 1, 2, 0))
  -> + SUM(IF ((timestamp BETWEEN NOW() AND NOW()+86400) AND tipo_accion = 2, 4, 0))
  -> + SUM(IF ((timestamp BETWEEN NOW() AND NOW()+86400) AND tipo_accion = 3, 6, 0))
  -> + SUM(IF ((timestamp BETWEEN NOW()+86401 AND NOW()+172800) AND tipo_accion = 1, 1, 0))
  -> + SUM(IF ((timestamp BETWEEN NOW()+86401 AND NOW()+172800) AND tipo_accion = 2, 2, 0))
  -> + SUM(IF ((timestamp BETWEEN NOW()+86401 AND NOW()+172800) AND tipo_accion = 3, 3, 0)) puntuacion
  -> FROM tabla group by id_usuario
 

No he realizado la consulta, primero tengo que estructurar bien la tabla, pero me gustaría saber si es la mejor forma de orientarlo según mis necesidades, suponiendo que el número de usuarios puede crecer mucho y el número de acciones también.

¿Alguna sugerencia de optimización?

Gracias de antemano.