sql - MySQL - Very simple Join is taking too long -

This is my first question in the StakeVarf Flow and I'm happy to be part of this community because this has helped me many times . / P>

I am not an expert in SQL and MySQL, but I am working in a project which requires large tables (million lines). I have a problem in getting involved and I do not understand why it takes so long thanks in advance :)

Here are the tables:

< pre> table Create (id int unique, codigo_alta is four (1), nombre varchar (100) present tabla_maestra, empresa_apellido1 varchar (150), apellido2 varchar (50), tipo_via varchar (20), nombre_via varchar (100 ), numero_via varchar (50), codigo_via four (5), codigo_postal four (5), nombre_poblacion varchar (100), codigo_ine four (11), nombre_provincia varchar (50), Telefono varchar (250) unique, Actividad varchar (100) , Estado four (1), codigo_operadora four (3)); Create table if Tablet_activated_press does not exist (Empress_appellido 1 varchar (150), activation varchar (100));

The query is what I want to do:

  update tabla_maestra TM Tae (= Tmknombre '' on tabla_actividades_empresas inner join and tae.empresa_apellido1 = Tm.empresa_apellido1) SET tm.actividad = tae.actividad;  

This query takes too long, and before executing it I was trying to test how long this simple query takes:

  SELECT COUNT (*) from tabla_maestra TM INNER Join tabla_actividades_empresas tae oN (tm.nombre! = '' and tae.empresa_apellido1 = tm.empresa_apellido1);  

It's still taking too long, and I do not understand why here are the indexes I use:

  create index Tabula_mestra (nombre) using cruce_nombre; Use index cruce_prepress_pallidado1 for tabla_master (empresa_apellido1); Make index indices_actividades_empresas tab using the hash on activated_prepress (empresa_apellido1);  

If I use Expand statement, then these results are:

I would be very grateful to receive an answer Can help me Many thanks, Dani.

Inclusion of one half million lines included - according to your query plan - - will take some time. Count (*) query faster because it tabla_maestra does not need to read the same table, but it still needs to scan the rows of index cruce_empresa_apellido1 .

If you index index_actividades_empresas a unique index (assuming that is really appropriate) or instead you drop the index and column empresa_apellido1 A primary key of the table is tabla_actividades_empresas .

To table

Even if you do not perform enough, only the other thing is to see what I do tabla_actividades_empresas is a synthetic primary key of type, and to match To change the related columns of tabla_mestra. It should help because integer integer comparison is faster than a string to compare the string, even if you can filter the (most) mismatch through a hash.