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.

Using FORCE INDEX
I am from Sweden and I've been in Kuala Lumpur for some time now and I wonder where I can find some good spots for shooting pictures. I've already been to Zoo Negara, but are there any other kind of places to visit? 

I will stay in Kuala Lumpur until December, feel free to make suggestions. I'm much into photographing people and life.
