Almost everywhere I read the use of FORCE INDEX 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.
However, recently I have found a case where FORCE INDEX improved my execution times in the range of hundred of times:
- JOIN on 4 tables
- first table has about 500 000 records
- 2 of the INNER JOINed table have more than 1 milion records
- first table has a field called published_date, stored as varchar in YMD format (could not changed to datetime)
- needed a range on published_date of at most 5 000 records
- some other GROUP BY and ORDER BY clauses on the first table on different fields than published_date were needed for this query
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 FORCE INDEX with published_date, the execution time dropped below 5 seconds.
It took me a few days to remember about the infamous FORCE INDEX option.
Based on suggestions I received on StackOverflow, I have issued ANALIZE TABLE and OPTIMIZE TABLE on the annoing table that does not want to use the index - unfortunately, it stiil doesn't use that index, so for now FORCE INDEX remains the only viable option. Below is the output for EXPLAIN after each step I took in my tryings to optimize the table:
without FORCE INDEX on table with alias a
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
with FORCE INDEX on table with alias a
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
after ANALYZE TABLE, without FORCE INDEX:
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
after OPTIMIZE TABLE, without FORCE INDEX:
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
after OPTIMIZE TABLE and ANALYZE TABLE, with FORCE INDEX
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
Questions:
- What are other use cases you found where FORCE INDEX saved you?
- Do you have some best practices when you consider using FORCE INDEX?
I have used FORCE INDEX a lot, usually when joining more tables on VARCHAR columns that are not PKs, and then having a date condition in the where clause.
ReplyDeleteMysql would not use the date index, so I would have to force it.
Here is a sample query, that I have also posted in stackoverflow:
SELECT NAME, a.reference_no, i.value, p.value FROM customers AS c
INNER JOIN accounts AS a ON c.id = a.customer_id
INNER JOIN invoices AS i ON i.reference_no = a.reference_no
INNER JOIN payments AS p ON p.invoice_no = i.invoice_no
WHERE payments.date >= '2011-09-01' AND DATE < '2011-10-01';
I would like mysql to use the index on payments.date, but in order to do so I have to force it