Re: Delete query not using index and cause deadlock
Hi,
What version of MySQL are you running? If MySQL 5.6 (or later), I suggest to try the following:
set optimizer_switch='use_index_extensions=off';
It seems there is a problem with cost estimates when there are many elements in the NOT IN list. One range will be set up for each interval between the values of the list, and if there are many ranges, the cost will be higher than a full table scan.
By turning off use_index_extensions, MySQL will not take advantage of the primary key part of the index, and there will be a single range scan over all records with the given grid_id. The disadvantage is that MySQL will have to do a look-up into the base table for every row in that range. There will be no filtering on primary index while scanning the index.
Feel free to file a bug report at bugs.mysql.com about this behavior.
Hope this helps,
Øystein Grøvlen,
Senior Principal Software Engineer,
MySQL Group, Oracle,
Trondheim, Norway