Extreme Performance-Unterschiede zwischen MariaDB und MySQL?

[Felix Riesterer]

Liebe Mitlesende,

ich stehe vor einem Rätsel. Auf dem Produktivsystem läuft ein MySQL-Server Version 5.7 und auf dem Test-/Entwicklungssystem ein MariaDB-Server Version 10.1.44. Eine spezielle Query (siehe unten) dauert auf dem MariaDB-Server 0,05 Sekunden, auf dem MySQL-Server stolze 20,5 Sekunden. In phpMyAdmin habe ich mir den Aufruf jeweils messen und erklären lassen. Die Ausgaben sind so unterschiedlich, dass ich nicht verstehe, warum und wo genau das Problem liegt.

Wer hat Hinweise oder Ideen, worin der Performance-Unterschied bei den beiden Servern liegen könnte?

Im Folgenden nun die Auflistungen unter "Detailliertes Profil":

MariaDB

Reihenfolge	Status	Zeit
1	Starting	19 µs
2	Waiting For Query Cache Lock	4 µs
3	Init	3 µs
4	Checking Query Cache For Query	12 µs
5	Checking Privileges On Cached	11 µs
6	Checking Permissions	11 µs
7	Checking Permissions	3 µs
8	Checking Permissions	3 µs
9	Checking Permissions	3 µs
10	Checking Permissions	3 µs
11	Checking Permissions	3 µs
12	Checking Permissions	3 µs
13	Checking Permissions	3 µs
14	Checking Permissions	3 µs
15	Checking Permissions	3 µs
16	Checking Permissions	4 µs
17	Checking Permissions	3 µs
18	Checking Permissions	3 µs
19	Checking Permissions	3 µs
20	Sending Cached Result To Clien	11 µs
21	Updating Status	4 µs
22	Cleaning Up	4 µs

MySQL

Reihenfolge	Status	Zeit
1	Sending Data	1,3 ms
2	Executing	5 µs
3	Sending Data	1,3 ms
4	Executing	5 µs
5	Sending Data	1,3 ms
6	Executing	5 µs
7	Sending Data	1,3 ms
8	Executing	5 µs
9	Sending Data	1,3 ms
10	Executing	4 µs
11	Sending Data	1,3 ms
12	Executing	5 µs
13	Sending Data	1,3 ms
14	Executing	5 µs
15	Sending Data	1,3 ms
16	Executing	5 µs
17	Sending Data	1,3 ms
18	Executing	4 µs
19	Sending Data	1,3 ms
20	Executing	5 µs
21	Sending Data	1,3 ms
22	Executing	5 µs
23	Sending Data	1,3 ms
24	Executing	5 µs
25	Sending Data	1,3 ms
26	Executing	5 µs
27	Sending Data	1,3 ms
28	Executing	5 µs
29	Sending Data	1,3 ms
30	Executing	5 µs
31	Sending Data	1,3 ms
32	Executing	5 µs
33	Sending Data	1,3 ms
34	Executing	5 µs
35	Sending Data	1,3 ms
36	Executing	5 µs
37	Sending Data	1,3 ms
38	Executing	4 µs
39	Sending Data	1,3 ms
40	Executing	4 µs
41	Sending Data	1,3 ms
42	Executing	5 µs
43	Sending Data	1,3 ms
44	Executing	5 µs
45	Sending Data	1,3 ms
46	Executing	5 µs
47	Sending Data	1,3 ms
48	Executing	5 µs
49	Sending Data	1,3 ms
50	Executing	4 µs
51	Sending Data	1,3 ms
52	Executing	4 µs
53	Sending Data	1,3 ms
54	Executing	4 µs
55	Sending Data	1,3 ms
56	Executing	5 µs
57	Sending Data	41 µs
58	Executing	3 µs
59	Sending Data	1,3 ms
60	Executing	4 µs
61	Sending Data	1,3 ms
62	Executing	5 µs
63	Sending Data	1,3 ms
64	Executing	4 µs
65	Sending Data	1,3 ms
66	Executing	4 µs
67	Sending Data	1,3 ms
68	Executing	5 µs
69	Sending Data	1,3 ms
70	Executing	5 µs
71	Sending Data	1,3 ms
72	Executing	5 µs
73	Sending Data	1,3 ms
74	Executing	5 µs
75	Sending Data	1,3 ms
76	Executing	5 µs
77	Sending Data	1,4 ms
78	Executing	5 µs
79	Sending Data	1,4 ms
80	Executing	5 µs
81	Sending Data	1,4 ms
82	Executing	5 µs
83	Sending Data	1,4 ms
84	Executing	4 µs
85	Sending Data	1,4 ms
86	Executing	5 µs
87	Sending Data	1,3 ms
88	Executing	5 µs
89	Sending Data	1,4 ms
90	End	8 µs
91	Query End	7 µs
92	Removing Tmp Table	8 µs
93	Query End	64 µs
94	Removing Tmp Table	4 µs
95	Query End	7 µs
96	Removing Tmp Table	13 µs
97	Query End	5 µs
98	Closing Tables	30 µs
99	Freeing Items	34 µs
100	Cleaning Up	12 µs

Der SQL-Code der Query ist 60 Zeilen lang:

SELECT DISTINCT
 `alt_exams`.`alt_exam`,
 `alt_exams`.`date`,
 `alt_exams`.`start`,
 `alt_bookings`.`student`,
 `people`.`sex`,
 `people`.`call_name`,
 `people`.`first_name`,
 `people`.`name_affix`,
 `people`.`family_name`,
 `people`.`name_suffix`,
 `students`.`form`,
 (
  SELECT DISTINCT `courses`.`subject`
  FROM `courses`
  WHERE `courses`.`course_name`=`alt_bookings`.`course_name`
  AND `courses`.`schedule`=(
   SELECT MAX(`courses`.`schedule`)
   FROM `courses`
   WHERE `courses`.`schedule`<`alt_exams`.`date`
  )
 ) AS `subject`,
 (
  SELECT GROUP_CONCAT(DISTINCT `lesson_teachers`.`teacher` ORDER BY `lesson_teachers`.`teacher` SEPARATOR '|%|')
  FROM `lesson_teachers`
  JOIN `lessons`
   ON `lesson_teachers`.`lesson_id`=`lessons`.`lesson_id`
  WHERE `alt_bookings`.`course_name`=`lessons`.`course_name`
  AND `lessons`.`schedule`=(
   SELECT MAX(`courses`.`schedule`)
   FROM `courses`
   WHERE `courses`.`schedule`<=`alt_exams`.`date`
  )
 ) AS `teachers`
FROM `alt_bookings`
JOIN `alt_exams`
 ON `alt_bookings`.`alt_exam`=`alt_exams`.`alt_exam`
JOIN `students`
 ON `alt_bookings`.`student`=`students`.`student`
JOIN `people`
 ON `students`.`person`=`people`.`person`
WHERE `alt_exams`.`date`>='2019-09-22'
AND EXISTS (
 SELECT `lesson_teachers`.`teacher`
 FROM `lessons`
 JOIN `lesson_teachers`
  ON `lessons`.`lesson_id`=`lesson_teachers`.`lesson_id`
 JOIN `staff`
  ON `lesson_teachers`.`teacher`=`staff`.`shorthand`
 JOIN `users`
  ON `staff`.`person`=`users`.`person`
 WHERE `lessons`.`course_name`=`alt_bookings`.`course_name`
 AND `lessons`.`schedule`=(
  SELECT MAX(`courses`.`schedule`)
  FROM `courses`
  WHERE `courses`.`schedule`<=`alt_exams`.`date`
 )
 AND `users`.`login`=''
 OR 1=1 -- privileged users --
);

Liebe Grüße

Felix Riesterer