One good source to refer to on FORCE INDEX would be the book MySQL Database Design and Tuning.
On page 120 paragraph 4, it says:
Where does FORCE INDEX fit in? FORCE INDEX (first enabled in version
4.0.9) is very similar to USE INDEX; the main difference between the two options is that FORCE INDEX demands that MySQL use the index (if
possible) in lieu of a more expensive table scan, whereas USE INDEX
still allows the optimizer to choose a table scan.
The same page says that MySQL does not warn that an index is irrelevant and switches to a table scan in the case of USE INDEX.
Thus, FORCE INDEX can take the MySQL Query Optimizer out of the equation before using the index. Any query using FORCE INDEX properly will reduce I/O. Why do I say properly?
Think about it. If the index you choose to navigate through is a covering index and you only need the columns as listed in the covering index, contacting the table for data becomes unnecessary. All I/O is restricted to index pages. All the data requested is retrieved by performing index scans in the worst case. That is indeed a good thing if the requested data needs to be ordered, thus bypass any requested sorting.
In terms of "rules of engagements", FORCE INDEX should only be used when
- referring to covering indexes
- all columns in the index are small in size
- you effectively tweek the caching behavior of SELECT queries
- For adhoc queries, use SQL_NO_CACHE
- For frequently updated data, cache judiciously
FORCE INDEX should not be made to force queries to use indexes if you are trying to fit a square peg in a round hole. In other words, traversing an index only to access table data in a specific order buys you nothing. In fact, it throws query performance under the bus because of not exercising any foreknowledge about how available your data needs to be.