MySQL Forums
Forum List  »  Performance

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

Options: ReplyQuote


Subject
Views
Written By
Posted
insert takes too long
2796
January 08, 2012 03:10AM
1193
January 09, 2012 01:25AM
1110
January 09, 2012 08:26AM
741
January 10, 2012 11:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.