Performance von AdminCourseFilter::getCourses() ist nicht optimal
Dort wird ein Query zusammengebaut, das ab einer gewissen Menge an Daten (in diesem Fall Ids von Einrichtungen) eher schlecht performt. Das wirkt sich auf der Verwaltungsseite für Veranstaltungen für Admins aus als auch auf jeder Verwaltungsseite einer Veranstaltung durch den Veranstaltungswähler in der Sidebar.
Das Optimieren des erzeugten Querys ist leider nicht ganz trivial. Ich hinterlasse hier mal ein Beispielquery, welches bei uns in Oldenburg um die 2 Sekunden dauert.
SELECT `seminare`.*
FROM `seminare`
INNER JOIN sem_types AS sem_types ON (sem_types.id = seminare.status)
INNER JOIN sem_classes AS sem_classes ON (sem_classes.id = sem_types.class)
INNER JOIN seminar_user AS teachers_su ON (teachers_su.Seminar_id = seminare.Seminar_id
AND teachers_su.status = 'dozent')
INNER JOIN auth_user_md5 AS teachers ON (teachers.user_id = teachers_su.user_id)
INNER JOIN seminar_inst AS seminar_inst ON (seminar_inst.seminar_id = seminare.Seminar_id)
WHERE (sem_classes.studygroup_mode = '0')
AND ((seminare.name LIKE '%5.02.242%'
OR seminare.VeranstaltungsNummer LIKE '%5.02.242%'
OR seminare.untertitel LIKE '%5.02.242%'
OR CONCAT(teachers.Vorname, ' ', teachers.Nachname) LIKE '%5.02.242%'))
AND (seminar_inst.institut_id IN ('631e9226d8b39e74b9e5190d313715cc',
'7cadba7e78aee20b315bb023f768245d',
'84683ef4ae7e8f0f32d3206b83d64c48',
'6109ffe9104cd4104be2bd5eb59ecf0a',
'9a7f7dc2ecd3041a37c7929b805d63ba',
'0e1840b44165c51dbdd899d5ae3581a1',
'a53966b8dfb7d449273f99eaa862c5eb',
'ffbc115311366c9a46f1ae310f27ed9f',
'82cb19e2fd0648187bd27f1ff4c6d8b0',
'9459c8230cde7316a8485a85281cc171',
'd6d25e0257dd26d67daff18dc6fe56dd',
'b3088bee942bd3c268f9122427373071',
'019e77b54d182429cb3d9cd447e54775',
'57abeb46e399578f83dee5d39d3c4d44',
'60f4e8f552ab44ed3167f1499cg034c6',
'69c13531794fba46210168000700e607',
'b38416c6fa613d93e193da0f6db29187',
'2276204755e58cf12db9c8ed32c418c2',
'828f3e3f7e5ffaa20b0168691cf800bd',
'8deacfabe408086bd5c22bffad8b41f5',
'1bd14aa6a004172a07c5703317269360',
'777a2642799cf8782980b188c333808a',
'c630e172a8194fab5261d5ce7fabdbee',
'8aa711994e5089fd07ac2a70f0d187e6',
'9d507bdf6ec9531c35032de39dc9690c',
'47e001095bd9bc3f72481eca9d7d6a7c',
'eeba929c5723644289dfd2b2ba84db9b',
'5cc7091b6fd8aa94f6ad0b9edc99a6a0',
'9b164d33312500bd3b5bdc0287934eab',
'730eb7752a9fe16ccee62e7987a6bb79',
'402c483dd133b2dee1b28de588951131',
'268de72fe36bdcc64781cf29a61f2576',
'7cd269c8abe26e7a708487f22369e068',
'49204a9934b0ebf57ae51fd584192ca7',
'b96290b26154c623d339855e4695a8e9',
'737a5e8ae682f8addfedf96456910744',
'9181502738f074c9b31307cbd4dd3df2',
'aa53a9f854d6e15c3823fa9354882a4e',
'448bde9e5a40f6e35202724950dc490e',
'78ccc435c77bfeb32545bbe37d90453e',
'0a14a01f00e56131f8edb692d6f49665',
'4e135617e8ff6a2b5de06fc539503185',
'e0ad6bc5d3f59aca175bd4c089867417',
'fe59f74b92361a477174fe2761a3ff79',
'e7dd8fe907c8f52e228ec646ada98559',
'1f1df81a6a3a2ed5e77c53d6554e7960',
'83a5ea6165b51def240213b401f98e4b',
'4d4263936e9a4de25d9bb9a5bdf5e403',
'55f23b04e4069bc3ae23179ff70446a4',
'4fe61d4465c12bc477664e0e3086d91d',
'd56a2289b8cd9749bbc1c9b29930419d',
'91f72fca4f527750ecef2e176fec6d53',
'f76ba98fb7a80b64261e50019b69a1fd',
'bd9bef9e20eed4a12729358a3d038ac9',
'97318c527b312bb8df46742bb9d85710',
'850794f0469d39f2b9f01cb8c67e7d26',
'8dd82dc9263247110e2e50fe71ad08f4',
'cc3cc7396c6623b131db50a8c98be6ca',
'168560c35eea1ecd3ddc8e3f3269fca2',
'df587cb0cce95ea8e9925b09e8782fea',
'e40b8d33cc35ad1a5735a6a13a05a5ff',
'48258280bef6b03e5d74f74f069e358d',
'd7104c8ee063408e967c81d97becc00f',
'b2003b419fd2fae9b6ec2f7d5b453d65',
'a6be4b9dcef98748847f51a84b209cad',
'e8cc4d3ec795f7e81bb06749acbabeb7',
'c6abe8e01f180d0e7674a6f6870874d9',
'20399ee991f44dd0efc5acd1bc098f50',
'12b36bcf31987c01956b09d5775ec60c',
'480ad97a1bb700cc3849bd8385bee90b',
'b041214e33992a557b0573eb61115fd5',
'380ecb14a325f88c36733a0a362bcd8b',
'00339de2a8bad6cb26a14338d915e020',
'cc9e53b59a2da417826ae275ce8394a6',
'3daa9fd8af3a3c497c9451f113bce53f',
'236b0ffba667e79ff4ef6f5a38503264',
'0eb68068586f0cdf0be8a53883cdafdf',
'234c35bf11a462b3f0020b3c94966096',
'2c2274b62e36e8d09299d84b65b69813',
'bcc716872310d91d53a1190ba29699ed',
'57a6e98e9380e4650cc9789365baad74',
'54f0cdb5f5030f01f6d8421fe29bf2d9',
'b7a067c02e1a81b08fbdd61d0bb5932e',
'f2972402437cf56cb7c5aa82466c9765',
'cdd394c615322c97e7eb04ff2fe16c73',
'afbea08c411a3be54e9dba7153371a46',
'0aef0041e934b988f29224a6d7e13a53',
'1edde8293f5ef1a948ab548ac4121447',
'000e3d20821c8acbe4fff56b7dbee656',
'cf5cd816a25a06884a0ded4f2a3cea62',
'e88811eee34651785d16fc1399c0b169',
'4fedfb8408393a497eea1663747dcd5b',
'907bb0c05c9cb41fbb15f005e3d94148',
'6b2a78f825363931502b69bf69bdaf35',
'53cf270f0b2e46a25395e64fd946a989',
'6f03cf81398fd2df8d481d4d9b40ba64',
'0e5a56ef6194c53a9c623d00abd85dee',
'bfca5d6d7cc900ffaa5c6270225d1ba6',
'c39d5069cc0134afd8a1bf7c5941d7e7',
'03125e059403ca861aad9edcfa6c07f3',
'96e65cf828ad718090c2a15327e52101',
'fada91d7d729f8f794e80ef93321c1df',
'24f171e0e26d5c16a5dc84aa28a77a07',
'2bfab55a5a8167602d2e6aa8133d282a',
'b0511a370d5f0d0654cc4cb7f7521039',
'1b68418c9b24c7505e0e74a5930d2702',
'952f8267a3de9d7d709b88d2e938ce36',
'149238ec97e785f755844b05fcd3eb66',
'd9de5f2bdb55241d9928cd4600a0e53b',
'd3b93933411405660cfcfc4832da670e',
'b8536fb6b60e1ac6f839351b76ab3037',
'c526bf80ff4f41f234f35a7caac0c7f8',
'be0736b61885b2495c549141ac85693a',
'1de85f47f284814b971ca2e06b3cce3c',
'f3e2ad64f534e8ed7c8a2039c640a5ad',
'13157eeff8b154b109793c1c60fd1eb7',
'd99346d8490a165bb59e2568c1235aba',
'220ad7384629b601d6a73f09e5c96e18',
'bd6065057985b8bfc328327808abf6dd',
'adf420b2a46dcf06dbb384fc016e0507',
'4a80a38db1239937795262124bb2d780',
'20aadf24cc2ce2c9e8556f44a3a319c9',
'81130c8061f68b3bd2809015e81d69c6',
'7cb266a36eea0f942d13a99dedc5b3d2',
'404bc4d76f79b780f0c4c475532d394a',
'ec2e364b28357106c0f8c282733dbe56',
'5273289ec6f8695b150d17ee9c8a55a5',
'b3846752c545012213021a8088e42267',
'14db760a839a4e87a2a1a15c42964eb9',
'260590d15de58e51ab1539cf8ca86a95',
'f4cf593439cb9ba3b809827e014f0f99',
'd1c2d22155e14c23c78c88501c4a20de',
'9ca0836052e841b224622c34dd722732',
'4eab4e4be6ba4b5eb6a2f6a67845e9f5',
'60d08794d1063af96a168d573ec5f196',
'46dd05dc170a3e1ae83cf0bd436b98c5',
'c5873431d3230f49d6fd55f26965f2cd',
'73708508ef4afd4957945b2dee4d154c',
'b9fee8ef7f81c991a21d9b6f66678bc5',
'62ab255d9d13376439445370c439c34f',
'e4d443e6a779ee72b3c6657302b535d0',
'cc94611353636a5a722566d26d8670d2',
'0ed8d32013c8b7575a4caeacde2609f4',
'a22b0bb2f0f0a5f2a7e1bdf4b816ff75',
'58c708844ae245567797276a4f4b6227',
'60d231b5da3d28b27d428df56d437835',
'c879f3f0979452bcb776ef9de218b7b0',
'f4ee667207ce91a5bd7afdf1c3fa7cb9',
'810ac4dd2e37edd0a9bc39b2289f4921',
'5ff190372c9e36339548f08155dcf1e2',
'a36226ce6d45c455e97ae37484e79c29',
'7c617d1448bf72c92b75642fb42e4355',
'a0b2a9ccdc7d0b59b9a3a151f826c1db',
'3686b286ad65e8ffb7d962122a95d6e7',
'f60eaa231ce3ac3428eb2492ce37a458',
'cfbace7137eefa9e88b8edf6b2dda152',
'e57d1464bb3320d12d6396b0609b4664',
'0dfa8bef4f61bdbf10ba21bc6fe81759',
'd50599b157b6ba4000c4a80a65b4bcce',
'bd70060df15f71417181ce8bc8537723',
'0c477e6934a4083985568c9b43d9abdb',
'4af5036b37434c98c2624e846458872f',
'9749c014037abcbe6b2eb37295931f40',
'f05aa0ffc4eb4f89d3271b05333bb7d3',
'b5522e512cb9f0681f83f816cd58c09c',
'aa1b2e44825dcaf5292941949e4535bf',
'81624f2a7871cb9589f4e325f0edda38',
'b33e3aa4bbb4b22b674b597a669380fa',
'e11037f6f541312dd714e90f1100956c',
'818a3fed601e570943fc2d91447c4e0c',
'bdeed2c51ad868316a5ae288b26bc267',
'fb4f5b2ca5082fd9b958f213f62af129',
'67b7fcfec69fd44744d372881332fccb',
'7d1c02cd5d740e798a793004d9122ed2',
'087cb851abb7c4565544b4cd84089e6a',
'3b54a58a230d2a6bef6dd60fa5ecec9b',
'6c0ac03f5595ee44c3d519c15aa06030',
'68db9449a96f053ae2986a6007fb1d6d',
'6bd030cb042a98473412a39791a628ed',
'eb4fd9bd9c45a5650b6b602053133cf7',
'b2ea792605bbcc13d795a570814e50e7',
'8435bd8f5d5b19d867dc62be897a5801',
'c340c2470772cafa7a43d2b727fddfc4',
'1a8e17fe158f2a56b1c1442623960e4d',
'492f12007cfec268e915849cba635eb4'))
AND (sem_types.class = 1)
GROUP BY seminare.Seminar_i