MySQL Bugs: #21300: Index hints for update statements

Bug #21300	Index hints for update statements
Submitted:	26 Jul 2006 15:44	Modified:	29 Oct 2019 21:15
Reporter:	Andre Timmer	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S4 (Feature request)
Version:	5.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
update table1, table2
where  ..join..

Is slow because of diffent table characteristics and MySQL uses the wrong order.
The real problem is that a cannot change to join order using something like 'update straight_join table1, table2 ...'.

How to repeat:
.

Suggested fix:
The problem in our case is that the update would be much faster (> 10 times) of the join order could be changed. I have proven this using a 'select straight_join'.

Suggested fix:
- allow index hints like 'straight_join' to be used with at least insert, update and delete
(- allow explain to be used with all statements)

Thank you for a reasonable feature requests.

It is possible to use straight_join between individual tables, just not for the entire query.  So there are work arounds.   You can also hint indexes with USE/FORCE/IGNORE INDEX, like in normal statements.

The following works:

UPDATE t1 straight_join t2 set t1.id=t2.id WHERE t1.id=t2.id;

This doesn't:

UPDATE straight_join t1, t2 set t1.id=t2.id WHERE t1.id=t2.id;

Our update is back in minutes instead of more than 3 hours.
Thanks a lot :-)

Found the documentation, although this option is easy.

Posted by developer:
 
UPDATE and DELETE statements fully support hints in 8.0 and later, see e.g WL#9158.