execution Time by adding index on mysql Table

Question

I'm changing an index on a table with more the 2million rows. The execution time is now longer then 3000 seconds. I think anything is working wrong on my pc because other changes before never took so long on the same table. I've added all indices with existing data. Has anybody an experience how much time does it take to change an index with 2 million existsing rows?

How can i check if anything is working fine on my local mysql installation?

Here is the table definition:

CREATE TABLE `sal_forwarding` (
  `sid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `f_shop` INT(11) NOT NULL,
  `f_offer` INT(11) DEFAULT NULL,
  `link` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `offerCode` VARCHAR(255) DEFAULT NULL,
  `sub2` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `forwardTime` DATETIME NOT NULL,
  `cid` CHAR(32) DEFAULT NULL,
  `f_partner` VARCHAR(20) NOT NULL,
  `t` VARCHAR(30) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT '0',
  `toolbarUser` TINYINT(1) NOT NULL DEFAULT '0',
  `blog` TINYINT(1) DEFAULT '0',
  `clickedPosition` INT(10) UNSIGNED DEFAULT NULL,
  `usedFrame` TINYINT(1) NOT NULL DEFAULT '0',
  `sub3` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `sub4` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `sub5` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `sub6` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `sub7` VARCHAR(200) CHARACTER SET latin1 COLLATE latin1_german1_ci DEFAULT NULL,
  `f_affiliateId` TINYINT(3) UNSIGNED NOT NULL,
  `browser` VARCHAR(50) DEFAULT NULL,
  `bversion` VARCHAR(5) DEFAULT NULL,
  `os` VARCHAR(50) DEFAULT NULL,
  `forwardDate` DATE NOT NULL,
  PRIMARY KEY (`sid`),
  KEY `f_shop` (`f_shop`,`forwardDate`,`cid`),
  KEY `f_partner` (`f_partner`,`forwardDate`),
  KEY `forwardDate` (`forwardDate`,`cid`),
  KEY `forwardDate_2` (`forwardDate`,`f_shop`),
  KEY `forwardDate_3` (`forwardDate`,`f_shop`,`f_partner`),
  KEY `forwardDate_4` (`forwardDate`,`f_partner`,`cid`),
  KEY `forwardDate_5` (`forwardDate`,`f_affiliateId`),
  KEY `forwardDate_6` (`forwardDate`,`f_shop`,`sid`)
) ENGINE=INNODB AUTO_INCREMENT=10747233 DEFAULT CHARSET=latin1;

Thank you very much.

Zohaib · Accepted Answer · 2011-10-26 10:01:37Z

1

Index creation on large table is relatively slow process, and although I am not an expert, but I believe there is nothing much that u can do in this regard.

Time does not only depend on the size of table, but it also largely depends upon the column on which index is being created. taking 3000 seconds is normal i guess, for table with 2 million rows, I once created composite index on a table with almost 3.5 million rows and it took more than 2 hours.....

Here are some links where people have shared about their experiences of creating index on large tables in MySQL

http://lists.mysql.com/mysql/212738

http://forums.mysql.com/read.php?24,356410,356889#msg-356889

answered Oct 26, 2011 at 10:01

Zohaib

7,1263 gold badges29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

execution Time by adding index on mysql Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related