Slow performance in mysql 5.6
Well. I tried to find out why altering table is so slow, but couldn't do.
I have table 'absences':
CREATE TABLE `absence` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_id` bigint(20) NOT NULL,
`date_from` datetime DEFAULT NULL,
`time_from` datetime DEFAULT NULL,
`date_to` datetime DEFAULT NULL,
`time_to` datetime DEFAULT NULL,
`type` int(11) NOT NULL,
`note` text,
`last_changer_id` bigint(20) NOT NULL,
`last_changing_date` datetime NOT NULL,
`updated` char(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `FKB8FB823DF9F3B1A1` (`user_id`),
KEY `FKB8FB823DE3872C93` (`last_changer_id`),
KEY `absence_date_from_idx` (`date_from`),
KEY `absence_date_to_idx` (`date_to`),
KEY `absence_type_idx` (`type`),
CONSTRAINT `FKB8FB823DE3872C93` FOREIGN KEY (`last_changer_id`) REFERENCES `app_user` (`id`),
CONSTRAINT `FKB8FB823DF9F3B1A1` FOREIGN KEY (`user_id`) REFERENCES `app_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
And then I try to add test column:
ALTER TABLE absence ADD COLUMN test_column INT;
The entire database isn't big: about 2Mb
On mysql 5.6.13 this query takes more than 1 sec, on mysql 5.0.51b it takes about 0.025 sec.
Could anyone please tell me why altering queries in mysql 5.6.13 (and in all versions since 5.6.9) take so much time? Maybe should I show some of the configuration properties from my.ini (i'm testing queries in Windows)?
Any help would be appreciated. Thanks.
PS. I've just created the table in both mysql servers (5.6.13 & 5.0.51):
CREATE TABLE `test_table` (
`test_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
And query INSERT INTO test_table VALUE(1); in mysql 5.6.13 takes about 0.03 sec while in mysql 5.0.51 the one takes 0.001 :( does anybody know why?
PSS. This problem is related to InnoDB engine tables. When I insert into MyISAM tables all is right.
Edited 3 time(s). Last edit at 08/02/2013 09:28AM by Egor Korobitsin.