Environment (Required on creation):  MySQL

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.

Steps to reproduce (Required on creation): 

Reproduction is not easy. See the linked support case for more information.

Observed Behavior (Required on creation):

Joining the authorization table on specific values can be slow due to using the IN operator in the ON clause. This slows down Cockpit.

Expected behavior (Required on creation):

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.

Root Cause (Required on prioritization):

Solution Ideas (Optional):

Change the mapping files in CE and EE to use OR operators instead of IN on MySQL only.

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

• https://mybatis.org/mybatis-3/dynamic-sql.html#Multi-db_vendor_support
• 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.

          AUTH ON (
          
          <if test="DB_TYPE == 'mysql'">
            AUTH.RESOURCE_ID_ = RES.PROC_DEF_KEY_
              OR
              <if test="authCheck.isHistoricInstancePermissionsEnabled">
                  AUTH.RESOURCE_ID_ = RES.ID_
                  OR
              </if>
              AUTH.RESOURCE_ID_ = '*')
          </if>
          <if test="DB_TYPE != 'mysql">
            AUTH.RESOURCE_ID_ in (
            <if test="authCheck.isHistoricInstancePermissionsEnabled">
              RES.ID_,
            </if>
            RES.PROC_DEF_KEY_, '*'))
          </if>
  

Hints (optional):

The mapping needs to be adjusted for CE and EE.