Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was in a presentation recently given by a Facebook MySQL engineer and he mentioned there that using FORCE INDEX reduces I/O.

It was something to do with the MySQL Query Optimizer not needing to go to the data dictionary and make calculations.

  • Can anyone explain how this works (preferably with references)?
  • Is this a good optimization practice to follow in general?
share|improve this question
4  
A quick Google query turned up the following blog post: A case for FORCE INDEX. –  Gonsalu Oct 28 '11 at 14:59
1  
I am almost certain that whatever Facebook does to optimize their MySQL environment counts as extreme and highly specialized optimization that only yields notable benefits at their massive, massive scale. The cost of using FORCE is in flexibility (your optimizer is bound) and maybe even performance (should you choose the wrong index, or should statistics change significantly). Still, I think the answers to this question should be informative. –  Nick Chammas Oct 28 '11 at 15:10
1  
Hey Jonathan, to make this question more relevant and useful to the site (and keep it from being closed), I edited it to solicit more concrete information and recommendations, as opposed to just links. –  Nick Chammas Oct 28 '11 at 15:58
add comment

1 Answer

One good source to refer to on FORCE INDEX would be the book MySQL Database Design and Tuning.

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.

share|improve this answer
1  
+1 Rolando, very concise –  Derek Downey Oct 28 '11 at 16:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.