(Resolvido) UPDATE e INNER JOIN em um banco muito grande

[chrystoffer]

Bom dia pessoal.
Estou tendo problemas com esse comando update e inner join. Tenho o bd X e Y. No bd Y tenho uma tabela chama "NOME" e "CODIGO". No bd X também tenho essas tabelas, mas o "NOME" está vazio pois tenho que importar esse nome do bd Y correspondente com o "CODIGO" das duas tabelas.

Por exemplo:

+---------bd X-----------------+

-----------------------------------

+---NOME--CODIGO-------+

+- - 40100 +

+ - 40101 +

--------------------------------------

+---------bd Y-----------------+

-----------------------------------

+---NOME--CODIGO-------+

+- CURITIBA - 40100 +

+ ACRE - 40101 +

------------------------------------

Preciso colocar os nomes da tabela Y na tabela X... eu utilizei esse comando em bd menores para testar:

UPDATE BD_X AS t1 
INNER JOIN BD_Y AS t2 
ON t1.CODIGO = t2.CODIGO 
SET t1.NOME = t2.NOME;

Funcionou no banco de dados menor tranquilamente... mas quando executei no bd de quase 9gb que tem mais de 16 milhões de linhas ele executou mas não gravou os dados... o que pode ser?

Editado Julho 20, 2015 por chrystoffer

[Denis Courcy]

MOstre as estruturas das tabelas

[chrystoffer]

Olá Denis, obrigado pela resposta. No momento não poderei mostrar pois estou executando um comando para testar... quando voltar tudo ao normal aqui coloco detalhadamente as tabelas.
Mas só para adiantar o que eu fiz, aumentei o valor do innodb_buffer_pool_size e executei esse comando agora:

UPDATE e13cv_teste n
  INNER JOIN (
    SELECT
      CD_ORGAO
    FROM e13cv_teste
    GROUP BY CD_ORGAO
  ) m ON n.CD_ORGAO = m.CD_ORGAO
  INNER JOIN e13m_teste o ON n.CD_ORGAO = o.CD_ORGAO
SET n.MUNICIPIO = o.MUNICIPIO

Aparentemente está indo... vamos ver se vai dar certo.Mais tarde comento aqui sobre se deu certo ou não... abraços

[Denis Courcy]

Pelo que você postou,

eu criaria uma tabela temporária com este select

SELECT
      CD_ORGAO
    FROM e13cv_teste
    GROUP BY CD_ORGAO

E um índice por CD_ORGAO para atender o inner join deste trecho:

) m ON n.CD_ORGAO = m.CD_ORGAO
  INNER JOIN e13m_teste o ON n.CD_ORGAO = o.CD_ORGAO

Seu inner join atual está deteriorado por falta de índice e está realizando table scan no resultado do select acima a cada linha lida em e13cv_teste.

Esta é a causa da lentidão.

[chrystoffer]

Obrigado pelas resposta Denis! Fiz as mudanças que você falou, agora esperando executar aqui.

Fiquei apenas com uma dúvida sobre porque criar uma tabela temporária? Ela ajuda em que especificamente ?

edit: Obrigado Denis. Fiz o índice no CD_ORGAO e funcionou normalmente agora :)

Editado Julho 20, 2015 por chrystoffer

[Denis Courcy]

Para completar e responder sua pergunta.

Tabela temporárias devem ser criadas em momentos especiais, como este, comum objetivo específico e para serem utilizadas por um breve período.

Elas podem ser físicas, de preferencia com engine MyISAM, quando a quantidade de dados for muito grande; e podem ser virtuais, com engine MEMORY, quando a quantidade de dados form pequena.

No seu caso a necessidade de evitar o table scan, que estava ocorrendo, foi a razão principal para o uso deste tipo de tabela.