Description (Required on creation; please attach any relevant screenshots, stacktraces, log files, etc. to the ticket):
MySQL chooses a complete range scan when using the IN operator when joining the authorization table on specific values. This can lead to very slow historic process instance queries.
Reproduction is not easy. See the linked support case for more information.
Joining the authorization table on specific values can be slow due to using the IN operator in the ON clause. This slows down Cockpit.
We use the OR operator instead of IN for joining the authorization table on specific values in historic process instance queries on MySQL systems. This should increase performance in some MySQL environments since a join buffer is used.
Since the slow part is not part of a top level statement we can not override it with DB-specific statements. We need to find out what DB system is currently used. This could be done like
- Create MySQL-specific statements for each statement that includes the slow query. Set a property (DB_TYPE) in each of the statements so we know if we are using MySQL or not.
The mapping needs to be adjusted for CE and EE.