Bug #77209 Update may use index merge without any reason (increasing chances for deadlock)
Submitted: 1 Jun 2015 11:31 Modified: 25 Feb 2016 18:38
Reporter: Andrii Nikitin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.24 OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 2015 11:31] Andrii Nikitin
Description:
On some conditions UPDATE query uses index merge when both indexes expect to retrieve 1 row.

This behavior increases chances for deadlock.

(Corresponding SELECT doesn't show index merge)

How to repeat:
drop table if exists a;

CREATE TABLE `a` (
  `ID` int  AUTO_INCREMENT PRIMARY KEY,
  `NAME` varchar(21),
  `STATUS` int,
  KEY `NAME` (`NAME`),
  KEY `STATUS` (`STATUS`)
) engine = innodb;

set @N=0;
insert into a(ID,NAME,STATUS)
select
	@N:=@N+1,
	@N%1600000, 
	floor(rand()*4)
 from information_schema.global_variables a, information_schema.global_variables b, information_schema.global_variables c 
LIMIT 1600000;

update a set status=5 where rand() < 0.005 limit 1;

explain UPDATE a SET STATUS = 2 WHERE NAME =  '1000000' AND STATUS = 5;

+-------------------------+--------------+------------------------------------------------------
| type        key         | key_len rows | Extra                                                
+-------------------------+--------------+------------------------------------------------------
| index_merge NAME,STATUS | 24,5       1 | Using intersect(NAME,STATUS); Using where; Using temp
+-------------------------+--------------+------------------------------------------------------

Suggested fix:
Do not use index merge when single index is good enough
Try to avoid using index merge in UPDATE to not provoke deadlocks
[25 Feb 2016 18:38] Paul Dubois
Noted in 5.6.30, 5.7.12, 5.8.0 changelogs.

For some queries, an Index Merge access plan was choosen over a range
scan when the cost for the range scan was the same or less.