4

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!

Toby
  • 41
  • 1
  • 2

2 Answers2

3

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

Michael Hampton
  • 244,070
  • 43
  • 506
  • 972
1

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.

FWI
  • 11
  • 1