MySQL Forums
Forum List  »  Performance

Re: Delete query not using index and cause deadlock
Posted by: Øystein Grøvlen
Date: August 21, 2015 07:13AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Delete query not using index and cause deadlock
2847
August 21, 2015 07:13AM


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.