MySQL Forums
Forum List  »  Performance

Re: MySQL 5.7 : InnoDB. 2 Deletes query using index but... too many rows locked and cause deadlock
Posted by: Christine Antoine
Date: July 18, 2019 04:01AM

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

Options: ReplyQuote

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.