InnoDB inserts very slow and slowing down

I have recently switched my project tables to InnoDB (thinking the relations would be a nice thing to have). I'm using a PHP script to index about 500 products at a time.

A table storing word/ids association:

    CREATE TABLE `windex` (
 `word` varchar(64) NOT NULL,
 `wid` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `count` int(11) unsigned NOT NULL DEFAULT '1',
 PRIMARY KEY (`wid`),
 UNIQUE KEY `word` (`word`)
) ENGINE=InnoDB AUTO_INCREMENT=324551 DEFAULT CHARSET=latin1

Another table stores product id/word id associations:

CREATE TABLE `indx_0` (
 `wid` int(7) unsigned NOT NULL,
 `pid` int(7) unsigned NOT NULL,
 UNIQUE KEY `wid` (`wid`,`pid`),
 KEY `pid` (`pid`),
 CONSTRAINT `indx_0_ibfk_1` FOREIGN KEY (`wid`) REFERENCES `windex` (`wid`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `indx_0_ibfk_2` FOREIGN KEY (`pid`) REFERENCES `product` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

The script was tested using MyISAM and it indexes products relatively fast (much, much faster than InnoDB). First time running in InnoDB it was ridiculously slow but after nesting more values together I ended up speeding it up by a lot (but not enough).

I would assume innodb would be much faster for this type of thing because of rowlevel locks but that's not the case.

I construct a query that looks something like:

SELECT
title,keywords,upc,...
FROM product
WHERE indexed = 0
LIMIT 500

I create a loop and fill an array with all the words that need to be added to windex and all the word id/product id pairs that need to be added to indx_0.

Because innodb keeps increasing my auto-increment values whenever i do a "REPLACE INTO" or "INSERT IGNORE INTO" that fails because of duplicate values, I need to make sure the values I add don't already exist. To do that I first select all values that exist using a query like such:

mysql performance innodb

Edit Report

InnoDB provides more complex keys structure than MyIsam (FOREIGN KEYS) and regenerating keys is really slow in InnoDB. You should enclose all update/insert statements into one transactions (those are actually quite fast in InnoDB, once I had about 300 000 insert queries on InnoDb table with 2 indexes and it took around 30 minutes, once I enclosed every 10 000 inserts into BEGIN TRANSACTION and COMMIT it took less than 2 minutes).

I recommend to use:

BEGIN TRANSACTION;
SELECT ... FROM products;
UPDATE ...;
INSERT INTO ...;
INSERT INTO ...;
INSERT INTO ...;
COMMIT;

This will cause InnoDB to refresh indexes just once not few hundred times.

Let me know if it worked

answered 2012-02-02T14:48:14.483

InnoDB inserts very slow and slowing down

1 Answer

Your Answer