latency issues due to userid mapping lookup

[mavwolverine]

🐛 Bug Report

We released supertokens to our production environment and everything went became slow then went down.
Analysis showed SELECT * FROM subscriberdb.st_userid_mapping WHERE app_id = 'public' AND (supertokens_user_id = '123' OR external_user_id = '123'); as the top slow query in RDS.

Useful informations

The problem with this query is, it uses OR which does not use indexes for those columns.

[mavwolverine]

And our site went down for second time due to row locking on the userid_mapping table.

[mavwolverine]

Running a custom core docker image with my fix, and (fingers crossed), so far the site is performing normal!

[mavwolverine]

Fixed in https://github.com/supertokens/supertokens-core/releases/tag/v10.0.0