Re: Delete query not using index and cause deadlock

[Rick James]

> INDEX(grid_id = 10, grid_role_id)
That was a typo; I meant
> INDEX(grid_id, grid_role_id)

And, as Øystein points out, INDEX(grid_id) is essentially that, since secondary keys have the PK appended. (An oversight on my part.)

> A very good idea! As you say, using this statement, the index scan will be separated from the base table look-up. Very smart!

Thanks for the compliment. I have spent years discovering subtle things like this.

> I do not think sorting the IDs would make any difference.

Yeah, I wasn't sure if it would apply here. When there are two DELETEs (or UPDATEs) in parallel, they can deadlock if they have some (or all) the same rows are involved but in different orders. By sorting, the deadlock and ROLLBACK turns into a delay as one transaction waits for the other to let go of row locks.

Øystein, I do not know if the query engine sorts the ids before acquiring row locks. But that _might_ be a deadlock-prevention feature.