Performance Issues After Migrating from MariaDB 10.3.39 to 10.6.16 Due to Changes in Query Execution Plan

Question

We recently migrated our database from MariaDB version 10.3.39 to 10.6.16 and have encountered significant performance issues with many of our older, mostly unoptimized queries. We've isolated one query as an example and noticed that the query execution plan differs greatly between the two versions. This specific query runs in 2 seconds on the old version but takes 30 seconds on the new version.

Here's the query in question:

SELECT SQL_NO_CACHE
`Users`.`EMAIL` AS `ID`,
`Users`.`ER_NR`,
`Users`.`SUB_ER_NR`,
`Users`.`USER_ID`,
`ANREDE`,
`PRAEFIX`,
`VORNAME`,
`ZWEITER_VORNAME`,
`NAME`,
`Users`.`EMAIL`,
IF(`IST_ANSPRECHPARTNER` = 'Ja', 'Ja', 'Nein') AS `IST_ANSPRECHPARTNER`,
(
        SELECT
                IF(COUNT(*) > 0, 'Ja', 'Nein') AS CC
        FROM
                `Users_Sell_Courses`
        WHERE
                `USER_ID` = `Users`.`USER_ID`
                AND `SELL_ID` IN (
                        SELECT
                                `SELL_ID`
                        FROM
                                `Sell_Courses`
                        WHERE
                                `COURSE_ID` = 63
                )
) AS `IST_PRAXISANLEITER`,
(
        SELECT
                IF(COUNT(*) > 0, 'Ja', 'Nein') AS CC
        FROM
                `Users_Sell_Courses`
        WHERE
                `USER_ID` = `Users`.`USER_ID`
                AND `SELL_ID` IN (
                        SELECT
                                `SELL_ID`
                        FROM
                                `Sell_Courses`
                        WHERE
                                `COURSE_ID` = 64
                )
) AS `IST_WUNDMANAGER`,
`Customer`.`STATUS`,
`Customer`.`FA_NAME`,
`PICK_ER_TYP`,
`BEGINN`,
`ENDE`,
`BLACKLIST`,
`MAILING`,
(
        SELECT
                COUNT(*)
        FROM
                `Sell_Courses`
        WHERE
                `Sell_Courses`.`SUB_ER_NR` = `Customer`.`SUB_ER_NR`
                AND (
                        YEAR(`VERTRAGSENDE`) = 0
                        OR `VERTRAGSENDE` > NOW()
                )
) AS `SoldCourses`,
`Package_Contracts`.`PACKAGE`
FROM
`Users`,
`Customer`
LEFT JOIN `Package_Contracts` ON (
        `Customer`.`SUB_ER_NR` = `Package_Contracts`.`SUB_ER_NR`
        OR `Customer`.`RECHNUNG_ZAHLER_SUB_ER_NR` = `Package_Contracts`.`SUB_ER_NR`
),
`Picklist_ER_Typ`
WHERE
`Users`.`SUB_ER_NR` = `Customer`.`SUB_ER_NR`
AND `Users`.`USER_STATUS` = 'Aktiv'
AND `Customer`.`ER_TYP` = `Picklist_ER_Typ`.`CUR_ID`
HAVING
`IST_ANSPRECHPARTNER` = 'Ja'
ORDER BY
`ID`

When we run EXPLAIN on both versions, we see that the index IDX_USERS_STATUS is not used in the new version, which we suspect is contributing to the issue.

EXPLAIN output for 10.3.39:

1   PRIMARY Users   ref SUB_ER_NR_2,SUB_ER_NR,IDX_USER_STATUS   IDX_USER_STATUS 1   const   165446  Using index condition; Using where; Using filesort
1   PRIMARY Customer    eq_ref  UQX-SUB_ER_NR,SUB_ER_NR,IDX_ER_TYP  UQX-SUB_ER_NR   20  Users.SUB_ER_NR 1   Using where
1   PRIMARY Picklist_ER_Typ eq_ref  PRIMARY PRIMARY 4   Customer.ER_TYP 1   
1   PRIMARY Package_Contracts   ALL SUB_ER_NR   NULL    NULL    NULL    524 Range checked for each record (index map: 0x2)
6   DEPENDENT SUBQUERY  Sell_Courses    ref IDX_SUB_ER_NR,IDX_VERTRAGSENDE  IDX_SUB_ER_NR   4   Customer.SUB_ER_NR  1   Using index condition; Using where
4   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
4   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where
2   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
2   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where

EXPLAIN output for 10.6.16:

1   PRIMARY Picklist_ER_Typ ALL PRIMARY NULL    NULL    NULL    19  Using temporary; Using filesort
1   PRIMARY Customer    ref IDX_ER_TYP  IDX_ER_TYP  5   Picklist_ER_Typ.CUR_ID  267 
1   PRIMARY Users   ref SUB_ER_NR_2,SUB_ER_NR,IDX_USER_STATUS   SUB_ER_NR_2 26  func    18  Using index condition; Using where
1   PRIMARY Package_Contracts   ALL SUB_ER_NR   NULL    NULL    NULL    599 Range checked for each record (index map: 0x2)
6   DEPENDENT SUBQUERY  Sell_Courses    ref IDX_SUB_ER_NR,IDX_VERTRAGSENDE  IDX_SUB_ER_NR   4   Customer.SUB_ER_NR  1   Using index condition; Using where
4   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
4   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where
2   DEPENDENT SUBQUERY  Users_Sell_Courses  ref IDX_USER_ID,IDX_SELL_ID IDX_USER_ID 8   Users.USER_ID   1   
2   DEPENDENT SUBQUERY  Sell_Courses    eq_ref  PRIMARY,IDX_SELL_ID,IDX_COURSE_ID   PRIMARY 4   Users_Sell_Courses.SELL_ID  1   Using where

We were able to optimize the query performance on 10.6.16 by adding FORCE INDEX(IDX_USER_STATUS) to the Users table.

However, we have a significant number of similar queries, and optimizing them all individually would be time-consuming. We're hoping to find a more efficient solution to this problem.

We have explored various optimizer switches within MariaDB in an attempt to revert to the previous behavior that our code relied on. However, we have not yet succeeded in achieving this.