MySQL Bugs: #36992: UPDATE query index hints

Bug #36992	UPDATE query index hints
Submitted:	26 May 2008 20:48	Modified:	13 Jan 2009 16:05
Reporter:	Steven Wittens	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.51a	OS:	Any (Mac OS X, Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	force index, index hints, update query

Description:
As of 5.0.51a at least, MySQL accepts index hints with UPDATE queries:

UPDATE table [USE|FORCE|IGNORE] INDEX (...) ...

The documentation does not mention this at all, for any version.

However, my test runs show that these hints are not being used when doing UPDATE ... ORDER BY, resulting in a slow filesort (query execution speed remains constant regardless of the index hints used). Applying the index hints to the matching SELECT .. ORDER BY query does have a noticeable effect.

This might be related to: http://bugs.mysql.com/bug.php?id=36569 which talks about index usage inconsistencies between matching (unhinted) UPDATE and SELECT queries. It would appear this inconsistency also applies when using index hints.

How to repeat:
Add an index hint to any UPDATE query, notice that MySQL does not throw a parse error.

Suggested fix:
Update the documentation to mention exactly how index hints are supported with UPDATE queries.

Thank you for te report.

Verified as described.

It turns out that index hints are accepted for UPDATE but are ignored and have no effect. I will mention this in the manual.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

I have updated the following sections to indicate that index hints are accepted but ignore for UPDATE:

http://dev.mysql.com/doc/refman/5.1/en/index-hints.html
http://dev.mysql.com/doc/refman/5.1/en/update.html