Estoy construyendo un clon de Tinder para un proyecto de estudio y estoy tratando de hacer algo muy simple conceptualmente, pero parece que mi solicitud es realmente demasiado pesada.

Estructura de datos

He creado este simple violín para visualizar la estructura de la base de datos.
Intenté poner índices en user.id user.gender * user.orientation match.user1 match.user2 match.createdAt sin suerte.

Resultado esperado

Quiero encontrar a las personas que tienen menos coincidencias según el sexo, la orientación, el último inicio de sesión y la fecha del calendario.
Los usuarios no deben formar parte de más de 4 partidos durante 24 horas, así que busco usuarios con <= 3 partidos durante las últimas 24 horas.

Los siguientes valores están codificados para facilitar la edición de la solicitud y porque no me tomé el tiempo para hacer esta parte por ahora.

Una coincidencia se compone de 2 usuarios (usuario1 y usuario2).
El límite de 4 coincidencias en el mismo día es una suma de cuando aparecen como usuario1 y usuario2.

SELECT total_sum, userId
      FROM (
        SELECT u.id as userId, u.orientation as userOrientation, u.gender as userGender, m1.sum1, m2.sum2, (m1.sum1 + m2.sum2) AS total_sum
        FROM user u
        INNER JOIN (
          SELECT user1, COUNT(user1) as sum1 
          FROM `match` 
          WHERE createdAt > DATE('2017-12-11 00:00:00') 
          GROUP BY user1
        ) m1
        ON m1.user1 = u.id
        INNER JOIN (
          SELECT user2, COUNT(user1) as sum2 
          FROM `match` 
          WHERE createdAt > DATE('2017-12-11 00:00:00') 
          GROUP BY user2
        ) m2
        ON m2.user2 = u.id
        WHERE u.gender IN ('female')
        AND u.orientation IN ('hetero', 'bi')
        AND u.lastLogin > 1512873464582
      ) as total
      WHERE total_sum < 4
      ORDER BY total_sum ASC
      LIMIT 8

La cuestión

Con tablas pequeñas, la solicitud lleva unos pocos ms, pero con tablas medianas (50k usuarios, 200k coincidencias), la solicitud lleva años (170s).

Optimizando

Según la respuesta de @Thorsten Kettner , esta es la explicar el plan de su solicitud cuando yo ejecutarlo en mi prueba db después de configurar los índices que aconsejó:

Solución

Terminé haciendo algo más fácil.
Primero, aplané mi tabla de coincidencias eliminando la columna user2. Duplicó el tamaño porque ahora 1 coincidencia se convierte en 2 filas, pero me permite hacer algo muy simple y muy eficiente con índices adecuados.
La primera consulta es para administrar usuarios sin coincidencias y la segunda para manejar usuarios con coincidencias. Ya no tengo matchLimit en la consulta, ya que agrega trabajo adicional para mysql y solo necesito verificar el primer resultado para ver si matchNumber es <= 3.

(SELECT u.id, mc.id as nb_match, u.gender, u.orientation
FROM user u 
LEFT JOIN match_composition mc 
ON (mc.matchedUser = u.id AND mc.createdAt > DATE('2017-12-11 00:00:00'))
WHERE u.lastLogin > 1512931740721 
AND u.orientation IN ('bi', 'hetero')
AND u.gender IN ('female')
AND mc.id IS NULL
ORDER BY u.lastLogin DESC)

UNION ALL 

(SELECT u.id, count(mc.id) as nb_match, u.gender, u.orientation
FROM match_composition mc
JOIN user u 
ON u.id = matchedUser
WHERE mc.createdAt > DATE('2017-12-11 00:00:00')
AND u.lastLogin > 1512931740721
AND u.orientation IN ('bi', 'hetero')
AND u.gender IN ('female')
GROUP BY matchedUser
ORDER BY nb_match ASC
LIMIT 8)

Gracias por su ayuda

0
user6465431354 12 dic. 2017 a las 05:43

3 respuestas

La mejor respuesta

Un usuario puede coincidir como usuario1 o usuario2. Podemos usar UNION ALL para obtener un registro por usuario:

select user1 as userid from match union all select user2 as userid from match;

La consulta completa:

select
  u.id as userid,
  coalesce(um.total, 0) as total
from user u
left join
(
  select userid, count(*) as total
  from 
  (
    select user1 as userid from match where createdat > date '2017-12-11'
    union all 
    select user2 as userid from match where createdat > date '2017-12-11'
  ) m
  group by userid
) um on um.userid = u.id
where u.gender IN ('female')
  and u.orientation in ('hetero', 'bi')
  and u.lastlogin > 1512873464582
  and coalesce(um.total, 0) < 4
order by coalesce(um.total, 0);

Tendría los siguientes índices para esto:

create index idx_m1 on match (createdat, user1);
create index idx_m2 on match (createdat, user2);
create index idx_u on user (lastlogin, gender, orientation, id);
2
Thorsten Kettner 12 dic. 2017 a las 11:19

Supongo que tenías razón sobre tus habilidades SQL. Esto es lo que se me ocurrió:

SELECT u.id as userId, 
       u.orientation as userOrientation, 
       u.gender as userGender, 
       count(m.user1) total_sum
FROM user u
LEFT JOIN `match` m on (u.id in (m.user1, m.user2) 
                        and m.createdAt > DATE('2017-12-11 00:00:00'))
WHERE u.gender IN ('female')
  AND u.orientation IN ('hetero', 'bi')
  AND u.lastLogin > 1512873464582
having count(m.user1) <=4
ORDER BY total_sum ASC
LIMIT 8;

Editar: Cubierto también casos sin coincidencias

Intente jugar con la indexación de coincidencias columnas de tabla usuario1, usuario1 y también con Usuario columnas de tabla (o combinaciones de columnas) que utiliza en los filtros (género por ejemplo), vea qué aporta un mejor rendimiento.

2
Edgars T. 12 dic. 2017 a las 07:30

A partir de lo que proporcione, crearía índices sobre:
- match.user1
- match.user2
- match.createdAt
- user.id (único, y probablemente un PK) - user.lastLogin

También intentaría reemplazar COUNT (usuario1) por COUNT (*), pero probablemente no tendrá un gran impacto.

Los índices en user.gender y user.orientation son probablemente inútiles: la eficiencia de un índice es de alguna manera proporcional a la varianza de sus valores subyacentes. Por lo tanto, un índice en un campo con 2-3 valores distintos es más costoso que útil.

En cuanto a la DLL, intente lo siguiente. Traté de forzar el filtrado en user antes de las uniones con match, en caso de que el optimizador de consultas no funcione correctamente (tengo poca experiencia con bases de datos que no son MS)

SELECT total_sum, userId
FROM (SELECT u.id as userId, u.orientation as userOrientation, u.gender as userGender, m1.sum1, m2.sum2, (m1.sum1 + m2.sum2) AS total_sum
      FROM (SELECT * FROM user 
            WHERE gender = 'female'
            AND orientation IN ('hetero', 'bi')
            AND lastLogin > 1512873464582
            ) u
      INNER JOIN (SELECT user1, COUNT(*) as sum1 
                  FROM `match` 
                  WHERE createdAt > DATE('2017-12-11 00:00:00') 
                  GROUP BY user1
                  ) m1 ON m1.user1 = u.id
      INNER JOIN (SELECT user2, COUNT(*) as sum2 
                  FROM `match` 
                  WHERE createdAt > DATE('2017-12-11 00:00:00') 
                  GROUP BY user2
                  ) m2 ON m2.user2 = u.id
      ) as total
WHERE total_sum < 4
ORDER BY total_sum ASC
LIMIT 8
0
Patrick Honorez 12 dic. 2017 a las 10:59