You are using a key with low cardinality (many rows match
the key value) through another column. In this case, MySQL
assumes that by using the key it probably will do many key
lookups and that a table scan would be faster.

For small tables, a table scan often is appropriate and the
performance impact is negligible. For large tables, try the
following techniques to avoid having the optimizer incorrectly
choose a table scan:

User Comments

We ran into a condition where we were trying to join on 2 varchar fields that used a different collation. One field was utf-8 and the other was latin1. This was causing a full table scan on a join between the 2 tables. Once we converted the fields to all be utf-8, query went from 9 seconds down to 0.