Performance issue in log in query

[xuwang-wish]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• This issue affects my Ory Cloud project.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Describe the bug

Description

We are planning to do a load test on kratos and the first step for that is to generate 4.1million test accounts. However once those accounts are generated, we found that kratos endpoint can not serve log in request (email + password). It always shows a "time out" as the response.

Checking the query on database side, we identified one slow query:

SELECT ic.identity_id FROM identity_credentials ic INNER JOIN identity_credential_types ict on ic.identity_credential_type_id = ict.id INNER JOIN identity_credential_identifiers ici on ic.id = ici.identity_credential_id WHERE ici.identifier = 'my_test_email@gmail.com' AND ic.nid = 'b3990cd5-a387-4f7c-860e-ac3c5730bb6f' AND ici.nid = 'b3990cd5-a387-4f7c-860e-ac3c5730bb6f' AND ict.name = 'password';

With no cache in place, this query takes around 1min to finish.

But if we added a new condition to the where clause, the query takes 0.05 second, which makes more sense.

SELECT ic.identity_id FROM identity_credentials ic INNER JOIN identity_credential_types ict on ic.identity_credential_type_id = ict.id INNER JOIN identity_credential_identifiers ici on ic.id = ici.identity_credential_id WHERE ici.identifier = 'my_test_email@gmail.com' AND ic.nid = 'b3990cd5-a387-4f7c-860e-ac3c5730bb6f' AND ici.nid = 'b3990cd5-a387-4f7c-860e-ac3c5730bb6f' AND ict.name = 'password' and ici.identity_credential_type_id = '78c1b41d-8341-4507-aa60-aff1d4369670';

The explain output also shows the difference between these two queries, note the rows and key_len field in 2.row.

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ict
partitions: NULL
type: const
possible_keys: PRIMARY,identity_credential_types_name_idx
key: identity_credential_types_name_idx
key_len: 130
ref: const
rows: 1
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ici
partitions: NULL
type: ref
possible_keys: identity_credential_identifiers_identifier_nid_type_uq_idx,identity_credential_id
key: identity_credential_identifiers_identifier_nid_type_uq_idx
key_len: 145
ref: const
rows: 2016635
filtered: 10.00
Extra: Using index condition
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ic
partitions: NULL
type: eq_ref
possible_keys: PRIMARY,identity_credential_type_id,identity_credentials_nid_fk_idx,identity_credentials_nid_idx
key: PRIMARY
key_len: 144
ref: wish_accounts_dev_kratos_backup.ici.identity_credential_id
rows: 1
filtered: 25.00
Extra: Using where
3 rows in set, 1 warning (0.06 sec)

VS

*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ict
partitions: NULL
type: const
possible_keys: PRIMARY,identity_credential_types_name_idx
key: identity_credential_types_name_idx
key_len: 130
ref: const
rows: 1
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ici
partitions: NULL
type: const
possible_keys: identity_credential_identifiers_identifier_nid_type_uq_idx,identity_credential_id,identity_credential_identifiers_type_id_fk_idx
key: identity_credential_identifiers_identifier_nid_type_uq_idx
key_len: 1312
ref: const,const,const
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ic
partitions: NULL
type: const
possible_keys: PRIMARY,identity_credential_type_id,identity_credentials_nid_fk_idx,identity_credentials_nid_idx
key: PRIMARY
key_len: 144
ref: const
rows: 1
filtered: 100.00
Extra: NULL
3 rows in set, 1 warning (0.05 sec)

Running environment

K8s + docker + AWS RDS (Amazon Aurora compatible with mysql 5.7)

Reproducing the bug

As explained above

Relevant log output

No response

Relevant configuration

No response

Version

0.8.2

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Kubernetes

Additional Context

No response

[aeneasr]

Thank you for the findings! Is it possible that there's an index missing? That's what it looks like to me!

[xuwang-wish]

That's correct.

The original query doesn't specify the desired value for ici.identity_credential_type_id in the where clause which causes the multi-column index on the table NOT to be fully used (thus smaller key_len). As a result the query needs to scan millions of records. (though as defined by the join clause, the result set will still be correct),

[aeneasr]

Ok, would you be open to add the appropriate key? :)

[xuwang-wish]

Yes, we are trying to fix it and then will contribute back to the community.

[aeneasr]

Awesome, thank you so much! :)