Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

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).

share|improve this answer
add comment

Look at the three 36-byte "ID" fields that you are INDEXing.

I would first suggest reducing those three down to and 8-byte UNSIGNED BIGINT; 4-byte UNSIGNED INT is even better if a 4-Billion number is a big enough.

IF the 4-byte INT "deleted" field is being used as a flag, this could be changed to a 1-byte TINYINT.

You can try issuing:


ALTER TABLE checklist_answer DISABLE KEYS;

/*

 Make you table changes in here.

*/

ALTER TABLE checklist_answer ENABLE KEYS;


Together, I wouldn't be surprised to see the "4 hour" cut in half.

Other Performance-Killers here is: "ENGINE=InnoDB DEFAULT CHARSET=utf8;".

Changing this to: "ENGINE=MYISAM DEFAULT CHARSET=latin1;" could reduce the time in half again.

BIGINTs are performance killers on 32-bit machines. 64-bit machine will run MariaDB 2.5 times faster than on a 32-bit machine.

share|improve this answer
add comment

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.