Edit Question - KnowledgeHub

Title Body

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:

Tags (comma-separated) Cancel