StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL `FORCE INDEX` use cases?
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload