Dropping Index on 10GB+ InnoDB table takes over 4 hours

Toby asked:

This is the table I’m working with:

CREATE TABLE IF NOT EXISTS `checklist_answer` (
  `id` varchar(36) NOT NULL,
  `created_by` varchar(36) NOT NULL,
  `date_created` datetime NOT NULL,
  `updated_by` varchar(36) NOT NULL,
  `date_updated` datetime NOT NULL,
  `deleted` int(11) NOT NULL,
  `checklistresponse_id` varchar(36) NOT NULL,
  `question_id` varchar(36) NOT NULL,
  `questionoption_id` varchar(36) DEFAULT NULL,
  `value` varchar(256) NOT NULL,
  `source` int(11) NOT NULL,
  `award_id` varchar(36) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `checklist_answer_1f92e550` (`question_id`),
  KEY `checklist_answer_35e0d13d` (`questionoption_id`),
  KEY `answerset` (`checklistresponse_id`,`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Currently the table has approximately 20 million rows and is about 12GB. Whenever I attempt to add a new index, or drop an index, it takes a minimum 4 hours. Is there something glaring that I’m doing wrong or is that just how it is?

MySQL 5.1.49

Thanks!

My answer:


If you’re using MySQL 5.1’s built-in InnoDB, then index creation and removal are very slow. This was addressed in 5.5 with fast indexes. Update MySQL if possible. Alternately you can replace the built in InnoDB from 5.1 with the InnoDB Plugin (though this should have already been done; given that you’re having this issue it probably somehow wasn’t).


View the full question and answer on Server Fault.

Creative Commons License
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.