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

Question

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         |          1 | cust_id          |            1 | cust_id          | A         |        1191 |     NULL | NULL   |      | BTREE      |         |
| tablename         |          1 | a_id             |            1 | a_id             | A         |      129856 |     NULL | NULL   |      | BTREE      |         |
| tablename         |          1 | r_id             |            1 | r_id             | A         |           9 |     NULL | NULL   |      | BTREE      |         |
| tablename         |          1 | status           |            1 | status           | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| tablename         |          1 | leg_id           |            1 | leg_id           | A         |      129856 |     NULL | NULL   |      | BTREE      |         |
| tablename         |          1 | date             |            1 | date             | A         |         806 |     NULL | NULL   |      | BTREE      |         |
| tablename         |          1 | time             |            1 | time             | A         |       64928 |     NULL | NULL   |      | BTREE      |         |
| tablename         |          1 | obj              |            1 | obj              | A         |      129856 |     NULL | NULL   |      | BTREE      |         |
+-------------------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+
9 rows in set (0.00 sec)

mysql> select count(distinct obj) from tablename;   
+--------------------------------+
| count(distinct obj) |
+--------------------------------+
|                         110120 |
+--------------------------------+
1 row in set (0.31 sec)

mysql> optimize table tablename;
+--------------------------------+----------+----------+----------+
| Table                          | Op       | Msg_type | Msg_text |
+--------------------------------+----------+----------+----------+
| db.tablename                   | optimize | status   | OK       |
+--------------------------------+----------+----------+----------+
1 row in set (0.13 sec)

mysql> analyze table tablename;
+--------------------------------+---------+----------+-----------------------------+
| Table                          | Op      | Msg_type | Msg_text                    |
+--------------------------------+---------+----------+-----------------------------+
| db.tablename                   | analyze | status   | Table is already up to date |
+--------------------------------+---------+----------+-----------------------------+
1 row in set (0.00 sec)

Create table statement:

mysql> show create table db.tablename\G;
*************************** 1. row ***************************
       Table: tablename
Create Table: CREATE TABLE `tablename` (
  `id` varchar(50) NOT NULL,
  `cust_id` varchar(50) NOT NULL,
  `a_id` varchar(50) NOT NULL,
  `a_em` varchar(255) NOT NULL,
  `r_id` varchar(50) NOT NULL,
  `obj` varchar(50) NOT NULL,
  `sub` varchar(250) NOT NULL,
  `txt` text NOT NULL,
  `status` tinyint(4) NOT NULL,
  `to` text NOT NULL,
  `cc` text NOT NULL,
  `bcc` text NOT NULL,
  `ev` tinyint(1) NOT NULL,
  `leg_id` varchar(50) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  PRIMARY KEY (`comm_id`),
  KEY `cust_id` (`cust_id`),
  KEY `a_id` (`a_id`),
  KEY `r_id` (`r_id`),
  KEY `status` (`status`),
  KEY `leg_id` (`leg_id`),
  KEY `date` (`date`),
  KEY `time` (`time`),
  KEY `obj` (`obj`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

ERROR: 
No query specified

We also tried moving the where clauses to the join condition, to no avail.

Answer 1

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!