Consider adding an index to event_txn_id(token_id)

[turt2live]

because there's a foreign key relationship with the access tokens table with a cascade delete, it could potentially speed things up by having an explicit index.

No idea if foreign keys automatically imply index though

[turt2live]

in live testing this doesn't appeared to have materially helped, but might be a good candidate for micro-optimization?

[DMRobertson]

No idea if foreign keys automatically imply index though

From the end of this section:

A foreign key must reference columns that either are a primary key or form a unique constraint. This means that the referenced columns always have an index (the one underlying the primary key or unique constraint); so checks on whether a referencing row has a match will be efficient. Since a DELETE of a row from the referenced table or an UPDATE of a referenced column will require a scan of the referencing table for rows matching the old value, it is often a good idea to index the referencing columns too. Because this is not always needed, and there are many choices available on how to index, declaration of a foreign key constraint does not automatically create an index on the referencing columns.

(I'm deliberately ignoring SQLite).

[DMRobertson]

What is the context here? Are you/typical deployments frequently deleting from the access tokens table? Is this known to be slow?

[turt2live]

What is the context here? Are you/typical deployments frequently deleting from the access tokens table? Is this known to be slow?

It was an attempt to make an operational issue with matrix.org slightly better, but ultimately didn't matter. Internal context can be provided if needed.

[H-Shay]

I don't see any reason why we shouldn't do this? It may help and should be pretty easy?