title: MySQL `FORCE INDEX` use cases?
body: <p>Almost everywhere I read the use of <code>FORCE INDEX</code> is highly discouraged and I perfectly understand and know why - there are immensely huge chances that MySQL knows better what indexes to choose than the (average) developer. </p>
  
  <p>However, recently I have found a case where <code>FORCE INDEX</code> improved my execution times in the range of hundred of times: </p>
  
  <ul>
  <li><code>JOIN</code> on 4 tables</li>
  <li>first table has about 500 000 records</li>
  <li>2 of the <code>INNER JOIN</code>ed table have more than 1 milion records</li>
  <li>first table has a field called <code>published_date</code>, stored as <code>varchar</code> in YMD format (could not changed to <code>datetime</code>)</li>
  <li>needed a range on <code>published_date</code> of at most 5 000 records</li>
  <li>some other <code>GROUP BY</code> and <code>ORDER BY</code> clauses on the first table on different fields than <code>published_date</code> were needed for this query</li>
  </ul>
  
  <p>Although I've rewritten the query in many ways, I was not able to get execution times smaller than 130 seconds (with highest being over 700 ). After using <code>FORCE INDEX</code> with <code>published_date</code>, the execution time dropped below 5 seconds. </p>
  
  <p>It took me a few days to remember about the infamous <code>FORCE INDEX</code> option.</p>
  
  <p><strong>Questions:</strong></p>
  
  <ul>
  <li>What are other use cases you found where <code>FORCE INDEX</code> saved you?</li>
  <li>Do you have some <strong>best practices</strong> when you consider using <code>FORCE INDEX</code>?</li>
  </ul>
  
  <p><strong>Edit - Obsevations:</strong>
  I created <a href="http://programming.tudorconstantin.com/2011/10/mysql-force-index-best-practices.html" rel="noreferrer">this blog post</a> with the question here also. All the answer you provide will appear there too - whith credits and all the stuff you want. </p>
  
  <p><strong>Edit 2</strong></p>
  
  <p>I applied the suggestions I received in your comments (<code>ANALYZE TABLE</code> and <code>OPTIMIZE TABLE</code>), below is the output of <code>EXPLAIN</code> applied on query - unfortunately the index selection is not at all better:</p>
  
  <h2> 1. without <code>FORCE INDEX</code> on table with alias <code>a</code>:</h2>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  am2 range   PRIMARY,idx_meta_article    idx_meta_article    4   NULL    275228  Using where; Using index; Using temporary; Using f...
  1   SIMPLE  a   eq_ref  PRIMARY,serial_issue_date_productid,pub_date,idx_d...   PRIMARY 4   mydb_toto.am2.ArticleID 1   Using where
  1   SIMPLE  ai  ref PRIMARY,idx_iso_article PRIMARY 4   mydb_toto.a.serial  11523   Using where; Using index
  1   SIMPLE  m   range   PRIMARY,meta_articles_type  meta_articles_type  4   NULL    96  Using where
  1   SIMPLE  am  eq_ref  PRIMARY,idx_meta_article    PRIMARY 8   mydb_toto.a.serial,mydb_toto.m.meta_id  1   Using where; Using index
  </code></pre>
  
  <h2> 2. with <code>FORCE INDEX</code> on table with alias <code>a</code>:</h2>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  a   range   pub_date    pub_date    11  NULL    17679   Using where; Using temporary; Using filesort
  1   SIMPLE  am2 ref PRIMARY,idx_meta_article    PRIMARY 4   mydb_toto.a.serial  21930   Using where; Using index
  1   SIMPLE  ai  ref PRIMARY,idx_iso_article PRIMARY 4   mydb_toto.a.serial  11523   Using where; Using index
  1   SIMPLE  m   range   PRIMARY,meta_articles_type  meta_articles_type  4   NULL    96  Using where
  1   SIMPLE  am  eq_ref  PRIMARY,idx_meta_article    PRIMARY 8   mydb_toto.am2.ArticleID,mydb_toto.m.meta_id 1   Using where; Using index
  </code></pre>
  
  <h2> 3. after <code>ANALYZE TABLE</code>, without <code>FORCE INDEX</code>:</h2>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  am2 range   PRIMARY,idx_meta_article    idx_meta_article    4   NULL    275228  Using where; Using index; Using temporary; Using f...
  1   SIMPLE  a   eq_ref  PRIMARY,serial_issue_date_productid,pub_date,idx_d...   PRIMARY 4   mydb_toto.am2.ArticleID 1   Using where
  1   SIMPLE  ai  ref PRIMARY,idx_iso_article PRIMARY 4   mydb_toto.a.serial  11523   Using where; Using index
  1   SIMPLE  m   range   PRIMARY,meta_articles_type  meta_articles_type  4   NULL    96  Using where
  1   SIMPLE  am  eq_ref  PRIMARY,idx_meta_article    PRIMARY 8   mydb_toto.a.serial,mydb_toto.m.meta_id  1   Using where; Using index
  </code></pre>
  
  <h2> 4. after <code>OPTIMIZE TABLE</code>, without <code>FORCE INDEX</code>:</h2>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  am2 range   PRIMARY,idx_meta_article    idx_meta_article    4   NULL    275228  Using where; Using index; Using temporary; Using f...
  1   SIMPLE  a   eq_ref  PRIMARY,serial_issue_date_productid,pub_date,idx_d...   PRIMARY 4   mydb_toto.am2.ArticleID 1   Using where
  1   SIMPLE  ai  ref PRIMARY,idx_iso_article PRIMARY 4   mydb_toto.a.serial  11523   Using where; Using index
  1   SIMPLE  m   range   PRIMARY,meta_articles_type  meta_articles_type  4   NULL    96  Using where
  1   SIMPLE  am  eq_ref  PRIMARY,idx_meta_article    PRIMARY 8   mydb_toto.a.serial,mydb_toto.m.meta_id  1   Using where; Using index
  </code></pre>
  
  <h2> 5. after <code>OPTIMIZE TABLE</code> and <code>ANALYZE TABLE</code>, with <code>FORCE INDEX</code>:</h2>
  
  <pre><code>id  select_type table   type    possible_keys   key key_len ref rows    Extra
  1   SIMPLE  a   range   pub_date    pub_date    11  NULL    17679   Using where; Using temporary; Using filesort
  1   SIMPLE  am2 ref PRIMARY,idx_meta_article    PRIMARY 4   mydb_toto.a.serial  21930   Using where; Using index
  1   SIMPLE  ai  ref PRIMARY,idx_iso_article PRIMARY 4   mydb_toto.a.serial  11523   Using where; Using index
  1   SIMPLE  m   range   PRIMARY,meta_articles_type  meta_articles_type  4   NULL    96  Using where
  1   SIMPLE  am  eq_ref  PRIMARY,idx_meta_article    PRIMARY 8   mydb_toto.am2.ArticleID,mydb_toto.m.meta_id 1   Using where; Using index
  </code></pre>