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

[Christine Antoine]

Thanks for the feedback Olivier

For the index on ID_ENV_CASE, I already have one => the one created with the foreign key : FK_CID_OPERATION_ENV_CASE

Why creating another one ?
And as you can see on both explain (the one I send you on the query on cid_operation), and the second one below.. it the index used apparently

Please find below the explain of the second query :
# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'o', NULL, 'range', 'PRIMARY,id_id_env_case,FK_CID_OPERATION_ENV_CASE', 'FK_CID_OPERATION_ENV_CASE', '9', NULL, '100', '100.00', 'Using where; Using index'
'1', 'SIMPLE', 'fp', NULL, 'ref', 'PRIMARY,UNQ_CID_FLIGHT_POINT', 'UNQ_CID_FLIGHT_POINT', '4', 'ERM_ACC.o.ID', '57', '100.00', 'Using index'
'1', 'DELETE', 'fpc', NULL, 'ref', 'FK_CID_FP_CID_FPC', 'FK_CID_FP_CID_FPC', '4', 'ERM_ACC.fp.ID', '1', '100.00', NULL