Slow DB Query for deleting the previous magic links

[Eliotboutet]

Hello, I'm facing an issue :
When you create a new magic link, there is a query in order to delete all expired magic links (static::deleteMagicLinkExpired();)

However, I experience some really bad performances on this query in production. Is it because I don't set any expiration date on the magic links ? I also noticed that there is no index present at the migrations level. There should be index optimization on the magic links table level.

Did anyone face this issue aswell ?
Thank you

[cesargb]

Hello!

The aim of this method is to keep the table clean, although it may not have been a good idea to allow a magiclink to be generated without expiration, as this may cause it to never get cleaned.

I have tested 400,000 records, obtaining a latency of 500-600ms to perform this action. By generating the indices for the available_at and max_visits columns this time improves to 10-20ms.

As a first step I think it is important to create these 2 indexes.

And a second measure is to not allow to generate magiclink without expiration date or at least that this parameter is configurable.