Currently, a query like historyService.createHistoricProcessInstanceQuery().variableValueEquals("foo", "a").list(); creates the following SQL:
The EXISTS part is a dependent subquery, i.e. a query that references a value of the outer result set. Such queries are slow on MariaDB and MySQL, because these systems are not able to rewrite this to a join.
- Rewrite the exists to a JOIN, probably LEFT JOIN
- variableValueEquals can be used multiple times in succession. We need to join ACT_HI_VARINST once for each of the conditions. Joining only once is not sufficient.
- This also applies when using or queries, i.e. we must join once for any variable condition, be it in an or query or in the "main" query
- This should be done in the core API as well as the webapp backend