Before reporting an issue

• I have read and understood the above terms for submitting issues, and I understand that my issue may be closed without action if I do not follow them.

Area

storage

Describe the bug

When deleting a realm with a large number of USER_CONSENT_CLIENT_SCOPE records, following slow query logs are recorded and the realm deletion operation failed with transaction timeout error:

# Time: 2024-05-12T01:23:45.000000Z
# User@Host: root[root] @  [127.0.0.1]  Id: 13895
# Query_time: 31.325039  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 14200000
SET timestamp=1715499224;
delete from USER_CONSENT_CLIENT_SCOPE where SCOPE_ID='1bb77779-8416-5f39-38ec-c741a7955262'; 

This is because there is no index for that table in column scope_id:

create index IDX_USCONSENT_SCOPEID on USER_CONSENT_CLIENT_SCOPE(SCOPE_ID);

After that there is another slow query because of the insidious bug of mariadb/mysql of not using indexes with inner queries:

# Time: 2024-06-01T05:43:21.000000Z
# User@Host: root[root] @  [127.0.0.1]  Id:   101
# Query_time: 570.563298  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 2110000
use root;
SET timestamp=1719463552;
delete from USER_CONSENT_CLIENT_SCOPE where USER_CONSENT_ID in (select userconsen1_.ID from USER_CONSENT userconsen1_ where userconsen1_.USER_ID in (select userentity2_.ID from USER_ENTITY userentity2_ where userentity2_.REALM_ID='dummy')); 

This last one is similar to other issues like this PR: #12611

Version

25.0.1

Regression

• The issue is a regression

Expected behavior

The realm should be deleted OK.

Actual behavior

Timeouts.

How to Reproduce?

Use MySQL (probably mariadb too) as the database.
You need a lot of consents in the table USER_CONSENT_CLIENT_SCOPE for a realm.
Then delete the realm.

Anything else?

No response