MySQL ignoring index, index cardinality is the same as number of records in the table (?!)

Everyone, I could use an extra set of eyes on an issue we've come across. Here's what we're seeing:

• We're writing some code to migrate our messages to a new table called

communications. We're migrating over a million rows.

• Right now, we are testing the migration with a smaller set (about 150,000 records)

• The query in phase 2 of the migration (which happens after all the

messages have been migrated) takes forever to run. Upon investigation
of the execution plan, we noticed MySQL is ignoring indexes on this
new table

• It turns out the cardinality of the index which we're using

to join is the same as the number of records in the table. So, MySQL
ignores the index because it thinks it is useless

• We confirmed that

the cardinality of this index should be lower, since the number of
uniques for this field is smaller than the number of records in the
table

I've attached some proof of what we're seeing. After some digging, I discovered MySQL bug 44059 which sounds like it is related: http://bugs.mysql.com/bug.php?id=44059

However, that bug seems to relate to partitioning, which we haven't done. Additionally, from the discussion on that thread, the version of MySQL we're using (5.1.55) appears to have this bug patched. Any thoughts are much appreciated!

```
mysql> show indexes from tablename;
+-------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
| tablename | 0 | PRIMARY | 1 | id | A | 129856 | NULL | NULL | | BTREE | |
| tablename

We discovered the solution thanks to a DBA who took a look. I wanted to leave the answer to help anyone else who may be in a similar situation.

The key was that the tables had different collations, but we were joining the tables on a varchar field. So, MySQL had to perform an implicit re-collation before it was able to join the tables on our join condition. Thus, it couldn't use any indexes.

So, to anyone else who comes across this: check your table collation if you are joining across varchar fields!

StackExchange Database Administrators Q#14863, answer score: 3