Ich habe eine grundlegende Frage zur Leistung von MySQL im Zusammenhang mit der Erklärung. Ich habe zwei Abfragen, die das gleiche Ergebnis zurückgeben, und ich versuche zu verstehen, wie man Sinn aus dem ergibtEXPLAIN der Ausführungspläne.

Die Tabelle enthält 50000 Datensätze, und ich führe einen Datensatzvergleich durch. Meine erste Abfrage dauert 18.625 Sekunden. Der Erklärungsplan ist wie folgt.

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

Meine nächste Abfrage hat 0,106 Sekunden gedauert, um ausgeführt zu werden ...

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

In der Dokumentation steht dasALL erfordert einen vollständigen Tabellenscan und das ist sehr schlecht. Das sagt es auchfilesort erfordert einen zusätzlichen Durchgang, um die Datensätze zu sortierenNot exists bedeutet, dass MySQL in der Lage war, eineLEFT JOIN Optimierung. Es ist auch klar, dass die erste Methode Indizes verwendet, während die zweite Methode dies nicht tut.

Ich versuche herauszufinden, was hier los ist und was Mathematik beinhaltet. ich renneRESET QUERY CACHE zwischen Tests, um sicherzustellen, dass man keinen unfairen Vorteil bekommt. 49520 x 413 x 4 ist viel kleiner als 50000 x 49520.

Hat es mit dem zu tun?id im Erklärungsplan?

Wenn ich diese und andere Abfragen teste, scheinen meine Beobachtungen zu sein, dass die Abfragekomplexität durch Multiplizieren von Elementen mit derselben ID und Addieren des Ergebnisses jeder ID zusammen angenähert werden kann ... Ist dies eine gültige Annahme?

Zusätzlich

Wie in den Kommentaren angefordert, hilft das Schema und die Abfragen nur für den Fall, aber ich suche keine besseren Abfragen ... Nur eine Erklärung derEXPLAIN. Der fragliche Tisch ...

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;

Die erste Abfrage ...

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;

Die zweite Frage ...

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

Mir ist auch aufgefallen, dass ich den Index fallen lasseeventScore dass die Abfrage auf 2,531 Sekunden abfällt und der Ausführungsplan sich nicht so sehr ändert, aber die Reihenfolge der possible_keys sich ändert und nichtUsing index für den Tischb (Ignoriere die geringfügigen Änderungen der Zeilenanzahl, die ich bei jeder Änderung des Schemas erzeuge.) ...

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