Tengo una pregunta básica de rendimiento de MySQL relacionada con la explicación. Tengo dos consultas que devuelven el mismo resultado y estoy tratando de entender cómo dar sentido a laEXPLAIN de los planes de ejecución.

La tabla tiene 50000 registros y estoy realizando una comparación de registros. Mi primera consulta tarda 18.625 segundos en ejecutarse. El plan de explicación es el siguiente.

id  select_type table   type    possible_keys                   key         key_len ref                                 rows    filtered    Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE      a       ALL     NULL                            NULL        NULL    NULL                                49520   100.00  
1   SIMPLE      b       ref     scoreEvent,eventScore           eventScore  4       olympics.a.eventId                  413     100.00      Using where; Using index; Not exists
1   SIMPLE      c       ref     PRIMARY,scoreEvent,eventScore   scoreEvent  8       olympics.a.score,olympics.a.eventId 4       100.00      Using where; Using index; Not exists

Mi siguiente consulta tiene 0.106 segundos para ejecutarse ...

id  select_type table       type    possible_keys   key     key_len     ref     rows    filtered    Extra
-----------------------------------------------------------------------------------------------------------------------------------
1   PRIMARY     <derived2>  ALL     NULL            NULL    NULL        NULL    50000   100.00      Using temporary; Using filesort
2   DERIVED     results     ALL     NULL            NULL    NULL        NULL    49520   100.00      Using filesort

En la documentación dice queALL Requiere un escaneo completo de la tabla y esto es muy malo. También dice quefilesort requiere un pase extra para ordenar los registros, también dice queNot exists significa MySQL fue capaz de hacer unaLEFT JOIN mejoramiento. También está claro que el primer método utiliza índices, mientras que el segundo método no lo hace.

Estoy tratando de averiguar qué está pasando aquí y qué matemática está involucrada. Estoy corriendoRESET QUERY CACHE entre pruebas para asegurar que a uno no se le da ningún tipo de ventaja injusta. 49520 x 413 x 4 es mucho más pequeño que 50000 x 49520.

¿Tiene que ver con laid en el plan de explicar?

Cuando estoy probando estas y otras consultas, parece que mis observaciones indican que la complejidad de la consulta se puede aproximar multiplicando los elementos con la misma ID y agregando el resultado de cada ID juntos. ¿Es esta una suposición válida?

Adicional

Tal como se solicita en los comentarios, el esquema y las consultas en caso de que sirvan, pero no estoy buscando mejores consultas ... Simplemente una explicación de laEXPLAIN. La mesa en cuestión ...

CREATE TABLE results (
  resultId INT NOT NULL auto_increment KEY, 
  athleteId INT NOT NULL,
  eventId INT NOT NULL,
  score INT NOT NULL,
  CONSTRAINT FOREIGN KEY (athleteId) REFERENCES athletes(athleteId),
  CONSTRAINT FOREIGN KEY (eventId) REFERENCES events(eventId),
  INDEX eventScore (eventId, score),
  INDEX scoreEvent (score, eventId)
) ENGINE=innodb;

La primera consulta ...

SELECT a.resultId, a.eventId, a.athleteId, a.score
FROM results a 

-- Find records with matching eventIds and greater scores
LEFT JOIN results b 
ON b.eventId = a.eventId 
AND b.score > a.score

-- Find records with matching scores and lesser testIds
LEFT JOIN results c
ON c.eventId = a.eventId
AND c.score = a.score
AND c.resultId < a.resultId

-- Filter out all records where there were joins
WHERE c.resultId IS NULL 
AND b.resultId IS NULL;

La segunda consulta ...

SELECT resultId, athleteId, eventId, score
FROM (
  SELECT resultId, athleteId, eventId, score
  FROM results
  ORDER BY eventId, score DESC, resultId
) AS a
GROUP BY eventId;

También me di cuenta de que si caía el índiceeventScore que la consulta se reduce a 2.531 segundos y el plan de ejecución no cambia mucho, pero el orden de las posibles teclas de teclas cambia y no lo esUsing index para la mesab (ignore los ligeros cambios en los recuentos de filas que estoy generando datos cada vez que cambio el esquema) ...

id  select_type table   type    possible_keys               key         key_len ref                                 rows    filtered    Extra
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
1   SIMPLE      a       ALL     NULL                        NULL        NULL    NULL                                47457   100.00  
1   SIMPLE      b       ref     eventId,scoreEvent          eventId     4       olympics.a.eventId                  659     100.00      Using where; Not exists
1   SIMPLE      c       ref     PRIMARY,eventId,scoreEvent  scoreEvent  8       olympics.a.score,olympics.a.eventId 5       100.00      Using where; Using index; Not exists