I attended the excellent Percona Live conference earlier this week, and I have to say it was easily the best conference I’ve ever attended. There were some great, very in-depth technical talks, which I’ll blog about later.

This post is prompted by this question posted on Twitter. At the closing talk given by Domas of Facebook, he mentioned that one should use FORCE INDEX when optimising for performance.

Why is this?

If you look at the manual page explaining how MySQL uses indexes you’ll see that they are broadly selected based on which will find the smallest number of rows. This means that as additional data is added to a table the ‘best’ index use to can change.

Using FORCE INDEX tells MySQL to use a specific index (or indexes) for a query. By using it, you’re avoiding the need to calculate which index to use for each query. You’re also ensuring (or at least making it more likely) that the indexes that are most used stay in the buffer pool, thus removing the need to read them from disk. MySQL also gets the opportunity to replace infrequently used indexes with data in the buffer pool.

I suspect that the benefits of FORCE INDEX don’t apply so much for those with smaller data sets, but when one has as massive a set of data as Facebook, many small optimisations make a big difference.

Update: See also this post by the big man himself :)