Recentemente, mudei minhas tabelas de projetos para o InnoDB (pensando que as relações seriam boas). Estou usando um script PHP para indexar cerca de 500 produtos por vez.

Uma tabela que armazena associação de palavras / IDs:

    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

Outra tabela armazena associações de identificação de produto / palavra-chave:

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

O script foi testado usando o MyISAM e indexa produtos relativamente rápido (muito, muito mais rápido que o InnoDB). A primeira vez que rodou no InnoDB foi ridiculamente lenta, mas depois de aninhar mais valores, acabei acelerando muito (mas não o suficiente

Suponho que o innodb seria muito mais rápido para esse tipo de coisa por causa dos bloqueios no nível de linha, mas esse não é o cas

Eu construo uma consulta que se parece com:

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

Crio um loop e preenche um array com todas as palavras que precisam ser adicionadas ao windex e com todos os pares de ids / ids de produtos que precisam ser adicionados ao indx_

Por que o innodb continua aumentando meus valores de incremento automático sempre que eu faço um "REPLACE INTO" ou "INSERT IGNORE INTO" que falha por causa de valores duplicados, preciso garantir que os valores adicionados não existam. Para fazer isso, primeiro seleciono todos os valores que existem usando uma consulta como esta:

SELECT wid,word
FROM windex
WHERE
word = "someword1" or word = "someword2" or word = "someword3" ... ...

Depois filtrei minha matriz com os resultados existentes, para que todas as novas palavras adicionadas sejam 100% nova

Isso leva cerca de 20% do tempo total de execução. Os outros 80% são adicionados aos valores do par em indx_0, para os quais existem muitos outros valore

Aqui está um exemplo do que recebo.

0,4806 segundos para selecionar produtos. (0,4807 s no total
0,0319 segundos para reunir 500 itens. (Total de 0,5126 s).
5,2396 segundos para selecionar valores windex para comparação. (5.7836 seg no total
1,8986 segundos para atualizar a contagem. (7.6822 seg no total
0,0641 segundos para adicionar 832 registros windex. (Total de 7,7464 s).
17,2725 segundos para adicionar um índice de 3435 pares pid / wid. (25.7752 s no total
operação demorou 26,07 segundos para indexar 500 produto

Os 3435 pares estão sendo executados em uma única consulta, como:

INSERT INTO indx_0(pid,wid)
VALUES (1,4),(3,9),(9,2)... ... ...

Por que o InnoDB é muito mais lento que o MyISAM no meu caso?