Re: MySQL 5.7 : InnoDB. 2 Deletes query using index but... too many rows locked and cause deadlock

[Olivier Dasini]

Hello,

The explain plan you get it is not necessary the explain plan used during the delete.
You can have the effective explain plan by using :
EXPLAIN FOR CONNECTION
https://dev.mysql.com/doc/refman/5.7/en/explain-for-connection.html

you can do a SHOW PROCESSLIST to get the query id than the EXPLAIN FOR CONNECTION <queryID>


If you can please send the result of :
SHOW CREATE TABLE cid_operation\G
and
ANALYZE TABLE cid_operation; SHOW TABLE STATUS LIKE 'cid_operation'\G

and same for cid_flight_point

In order to get a better idea what the schema is.

Anyway I agree with you an index on (id_env_case,id) could be useful in this case.

Cheers,
Olivier