Before reporting an issue

[X] 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:

```

1. Time: 2024-05-12T01:23:45.000000Z
2. User@Host: root[root] @ [127.0.0.1] Id: 13895
3. 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:

```

1. Time: 2024-06-01T05:43:21.000000Z
2. User@Host: root[root] @ [127.0.0.1] Id: 101
3. 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: https://github.com/keycloak/keycloak/pull/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