Extreme Performance-Unterschiede zwischen MariaDB und MySQL?

[Felix Riesterer]

Lieber Christian,

ich habe den Cache auf 0 gesetzt und sehe, dass dann ebenfalls 100 Teilschritte entstehen. Sie dauern aber Microsekunden, anstatt Millisekunden. Das mag daran liegen, dass auf meinem Testsystem der Apache und der MariaDB-Server auf derselben Maschine liegen, auf dem Webspace aber nicht.

Ein EXPLAIN sowie die Datenstrukturen sollten bei so einer Frage nicht fehlen.

Das sieht bei beiden sehr ähnlich aus:

MariaDB

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY	alt_exams	ALL	PRIMARY	NULL	NULL	NULL	18	Using where; Using temporary
1	PRIMARY	alt_bookings	ref	PRIMARY,alt_exam,student	PRIMARY	4	school_2019_2020.alt_exams.alt_exam	9	Using where
1	PRIMARY	students	eq_ref	PRIMARY,person	PRIMARY	4	school_2019_2020.alt_bookings.student	1
1	PRIMARY	people	eq_ref	PRIMARY	PRIMARY	4	school_2019_2020.students.person	1
6	SUBQUERY	users	index	person	person	6	NULL	78	Using index
6	SUBQUERY	staff	eq_ref	PRIMARY,shorthand	PRIMARY	4	school_2019_2020.users.person	1	Using where
6	SUBQUERY	lesson_teachers	ref	PRIMARY,lesson_id,teacher	teacher	62	school_2019_2020.staff.shorthand	80	Using where; Using index
6	SUBQUERY	lessons	eq_ref	PRIMARY	PRIMARY	4	school_2019_2020.lesson_teachers.lesson_id	1
7	DEPENDENT SUBQUERY	courses	index	PRIMARY,schedule	schedule	5	NULL	4479	Using where; Using index
4	DEPENDENT SUBQUERY	lessons	ref	PRIMARY,schedule,course_name	course_name	602	school_2019_2020.alt_bookings.course_name	10	Using index condition; Using where
4	DEPENDENT SUBQUERY	lesson_teachers	ref	PRIMARY,lesson_id	PRIMARY	4	school_2019_2020.lessons.lesson_id	1	Using index
5	DEPENDENT SUBQUERY	courses	index	PRIMARY,schedule	schedule	5	NULL	4479	Using where; Using index
2	DEPENDENT SUBQUERY	courses	eq_ref	PRIMARY,schedule,course_name	PRIMARY	605	func,school_2019_2020.alt_bookings.course_name	1	Using where; Using temporary
3	DEPENDENT SUBQUERY	courses	index	PRIMARY,schedule	schedule	5	NULL	4479	Using where; Using index

MySQL

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY	alt_exams	NULL	ALL	PRIMARY	NULL	NULL	NULL	18	33.33	Using where; Using temporary
1	PRIMARY	alt_bookings	NULL	ref	PRIMARY,alt_exam,student	PRIMARY	4	school.alt_exams.alt_exam	23	100.00	Using where
1	PRIMARY	students	NULL	eq_ref	PRIMARY,person	PRIMARY	4	school.alt_bookings.student	1	100.00	NULL
1	PRIMARY	people	NULL	eq_ref	PRIMARY	PRIMARY	4	school.students.person	1	100.00	NULL
6	DEPENDENT SUBQUERY	users	NULL	index	person	person	4	NULL	78	100.00	Using index
6	DEPENDENT SUBQUERY	staff	NULL	eq_ref	PRIMARY,shorthand	PRIMARY	4	school.users.person	1	100.00	Using where
6	DEPENDENT SUBQUERY	lesson_teachers	NULL	ref	PRIMARY,lesson_id,teacher	teacher	62	school.staff.shorthand	160	100.00	Using where; Using index
6	DEPENDENT SUBQUERY	lessons	NULL	eq_ref	PRIMARY	PRIMARY	4	school.lesson_teachers.lesson_id	1	100.00	NULL
7	DEPENDENT SUBQUERY	courses	NULL	ALL	PRIMARY,schedule	NULL	NULL	NULL	4479	33.33	Range checked for each record (index map: 0x5)
4	DEPENDENT SUBQUERY	lessons	NULL	ref	PRIMARY,schedule,course_name	course_name	602	school.alt_bookings.course_name	21	100.00	Using index condition; Using where
4	DEPENDENT SUBQUERY	lesson_teachers	NULL	ref	PRIMARY,lesson_id	lesson_id	4	school.lessons.lesson_id	1	100.00	Using index
5	DEPENDENT SUBQUERY	courses	NULL	ALL	PRIMARY,schedule	NULL	NULL	NULL	4479	33.33	Range checked for each record (index map: 0x5)
2	DEPENDENT SUBQUERY	courses	NULL	ref	PRIMARY,subject,schedule,course_name	PRIMARY	3	func	559	10.00	Using where; Using temporary
3	DEPENDENT SUBQUERY	courses	NULL	ALL	PRIMARY,schedule	NULL	NULL	NULL	4479	33.33	Range checked for each record (index map: 0x5)

Hier werden drei temporäre Tabellen erstellt. Außerdem wird das Ergebnis sehr fragmentiert übertragen (siehe die vielen executing und sending-Einträge).

Ich kann es noch nicht besser, sonst würde ich meine Anfrage sicherlich günstiger formulieren.

Ist der MySQL-Server auf der gleichen Maschine?

Offensichtlich nicht. Es handelt sich um ein shared webhosting Paket.

Ist Netzwerk ein Flaschenhals?

Das kann ich nicht beurteilen.

Wie groß ist das Resultset?

Weniger als 500 Zeilen.

Wie sieht die Speicher-Auslastung auf der DB-Maschine aus? Prüfe auch, ob der Tablespace für temporäre Tabellen auf einer drehenden Platte ist oder auf einer SSD.

Das ist in einem Rechenzentrum... k.A.

Drei correlating subqueries – ist vielleicht auch nicht so optimal. Das könnte schon auch ein Grund für die temporären Tabellen sein. Kannst du das vielleicht umformulieren?

Wenn ich wüsste, wie man das macht! Momentan tendiere ich dazu, die Abfrage allgemeiner zu halten und die Zuordnungen per PHP-Script zu leisten.

Liebe Grüße

Felix Riesterer