MySQL Forums
Forum List  »  Performance

Re: Delete query not using index and cause deadlock
Posted by: Øystein Grøvlen
Date: August 24, 2015 02:35AM


Rick James Wrote:
> I wonder if the _composite_ index
> INDEX(grid_id = 10, grid_role_id) would help?

If MySQL 5.6 or later, this index should not be necessary. MySQL 5.6 will be able to take advantage of that any InnoDB index contain the primary key. Hence, the
existing index on grid_id should be sufficient.

> Perhaps it would also require reformulating the
> query to
>     FROM `customgrid_grid_role` t1
>     JOIN `customgrid_grid_role` t2  USING
> (grid_role_id)  -- assuming that is the PK
>        WHERE t2.grid_id = 10
>          AND t2.grid_role_id NOT IN (...);
> (I am not sure of the syntax.)
> The hope in my reformulation is that it will do an
> _index_ scan on t2, then DELETE from t1. This
> _might_ further avoid the contention.
> Øystein, how does that sound?

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

> A different trick is to _sort_ the ids before
> launching into the DELETE (or other query). That
> tends to avoid _deadlocks_ by turning them into
> "waits". The problem with your query is that it's
> not easy to get the list of ids, much less, to
> sort them.

In this case, I do not think sorting the IDs would make any difference. Since one is looking for rows with a single grid_id index, the rows will be accessed in primary key order both when grid_id index is scanned and when a table scan is used.

Another question is where the primary keys used in the NOT IN expression comes from. If those are collected from a different query, may be that query could merged into the delete statement using either a join or a sub-query. That could give better performance than pulling the primary keys into the client in order to build the delete statement.

Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway

Options: ReplyQuote

Written By
Re: Delete query not using index and cause deadlock
August 24, 2015 02:35AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.