insert takes too long
Posted by:
nir elbaz
Date: January 08, 2012 03:10AM
Hi,
I have a problem i cant figure out:
when i am running a select
select actor_id,movie_id from
(select trim(att1),b.actor_id,trim(CONCAT(att2,att3,att4,att5,att6,att7,att8,att9,att10)) m_name
from data_stg a LEFT JOIN actors b
on trim(att1)=b.actor_name
where item_id>=240
and length(trim(att1))>0) a,movies b
where a. m_name=b.imdb_title
it take only few secondes but when trying to do
insert into actors_movies (actor_id,movie_id)
(select actor_id,movie_id from
(select trim(att1),b.actor_id,trim(CONCAT(att2,att3,att4,att5,att6,att7,att8,att9,att10)) m_name
from data_stg a LEFT JOIN actors b
on trim(att1)=b.actor_name
where item_id>=240
and length(trim(att1))>0) a,movies b
where a. m_name=b.imdb_title);
the insernt doesnt finish in a reasnoable time
more than 6 hours,
some technical issues:
1.actors_movies,data_stg,actors are myISAM
movies-innoDB
2.each table have around 2-3 milion recordes
Thanks,
Nir