Skip navigation links

MySQL Forums :: Performance :: insert takes too long


Advanced Search

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 1571 nir elbaz 01/08/2012 03:10AM
Re: insert takes too long 649 Rick James 01/09/2012 01:25AM
Re: insert takes too long 660 nir elbaz 01/09/2012 08:26AM
Re: insert takes too long 452 Rick James 01/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.