OER Campus: Suche nach Autoren von Materialien hat schlechte Performance
Die Suche im OER Campus bietet auch die Möglichkeit, nach den Autoren von Materialien zu suchen. Das zugrundeliegende Query hat aber ab einer gewissen Datengröße eine sehr schlechte Performance.
Dort wird ein Query wie das folgende erzeugt:
SELECT `oer_material`.*
FROM `oer_material`
LEFT JOIN oer_hosts ON (oer_hosts.host_id = oer_material.host_id)
LEFT JOIN oer_tags_material ON (oer_material.material_id = oer_tags_material.material_id)
LEFT JOIN oer_tags ON (oer_tags_material.tag_hash = oer_tags.tag_hash)
LEFT JOIN oer_material_users ON (oer_material_users.material_id = oer_material.material_id)
LEFT JOIN external_users ON (oer_material_users.user_id = external_users.external_contact_id
AND oer_material_users.external_contact = '1')
LEFT JOIN auth_user_md5 ON (oer_material_users.user_id = auth_user_md5.user_id
AND oer_material_users.external_contact = '0')
WHERE (draft = '0')
AND ((oer_material.host_id IS NULL
OR oer_hosts.`active` = '1'))
AND ((oer_material.name LIKE '%englisch%'
OR oer_material.description LIKE '%englisch%'
OR oer_material.short_description LIKE '%englisch%'
OR oer_tags.name LIKE '%englisch%'
OR external_users.name LIKE '%englisch%'
OR CONCAT(auth_user_md5.Vorname, ' ', auth_user_md5.Nachname) LIKE '%englisch%'))
AND (((difficulty_start <= '1'
AND difficulty_end >= '1')
OR (difficulty_start <= '12'
AND difficulty_end >= '12')
OR (difficulty_start <= '1'
AND difficulty_end >= '12')
OR (difficulty_start >= '1'
AND difficulty_end <= '12')))
GROUP BY oer_material.material_id
ORDER BY mkdate DESC
Durch die vielen Left Joins, die auch noch verkettet sind, kann von oer_material_users
zu auth_user_md5
bzw. external_users
kein Index verwendet werden (so zumindest meine Analyse).
Der Explain für das Query sieht folgendermassen aus:
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | oer_material | ALL | host_id | NULL | NULL | NULL | 428 | Using where; Using temporary; Using filesort |
1 | SIMPLE | oer_hosts | eq_ref | PRIMARY | PRIMARY 34 | oer_material.host_id | 1 | Using where | |
1 | SIMPLE | oer_tags_material | ref | unique_tags,material_id | material_id | 34 | studip_ol_uni.oer_material.material_id | 3 | Using index |
1 | SIMPLE | oer_tags | eq_ref | PRIMARY | PRIMARY | 34 | oer_tags_material.tag_hash | 1 | Using where |
1 | SIMPLE | oer_material_users | ref | PRIMARY | PRIMARY | 130 | func | 1 | Using where; Using index |
1 | SIMPLE | external_users | ALL | NULL | NULL | NULL | NULL | 478 | Using where; Using join buffer (flat, BNL join) |
1 | SIMPLE | auth_user_md5 | ALL | NULL | NULL | NULL | NULL | 39357 | Using where; Using join buffer (incremental, BNL join) |
Die letzten beiden Zeilen sind diejenigen, die die Probleme machen.
Wir haben die Suche nach Autoren lokal bei uns ausgebaut und wo das Query vorher weit über 180 Sekunden gebraucht hat, flutscht es nun in 100 Millisekunden durch.
Ich weiß nicht, ob man hier etwas optimieren kann oder ob es sich lohnt, das Query in zwei Teile aufzubrechen. Eventuell brauchen wir aber die Suche nach Autoren gar nicht und könnten diese einfach ausbauen.
Vielleicht habt Ihr ja eine Idee, wie man da rangehen könnte, @anoack, @elmar oder @Krassmus?