Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is the table using InnoDB or MyISAM ??? –  RolandoMySQLDBA Mar 12 '12 at 20:14
    
Sorry - the table is MyISAM –  Christopher Armstrong Mar 12 '12 at 20:18
    
Please display SHOW CREATE TABLE tablename\G –  RolandoMySQLDBA Mar 12 '12 at 20:22
    
@RolandoMySQLDBA - done. Thanks for taking a look –  Christopher Armstrong Mar 12 '12 at 20:28
    
You said We also tried moving the where clauses to the join condition, to no avail. Please show one or two queries having a problem. –  RolandoMySQLDBA Mar 12 '12 at 20:47

1 Answer 1

up vote 3 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.