Inserting identical data to 2 tables efficiently

[kickstart]

Hi

 

Minor and strange idea, and something I am a bit lumbered with due to some fairly nasty existing tables I am working with.

 

I have a fairly complex piece of SQL which unfortunately depends on some data derived elsewhere (a list of users, but with the ids manipulated). I have generated this list of users and put them into a temp table.

 

Unfortunately my main piece of SQL needs to join onto this table of data twice (once for each of a pair of unioned sub queries). MySQL will not allow a temp table to be used more than once in a single query, hence I have just created 2 identical temp tables and populated them twice.

 

This works fine but I am wondering if there are any efficient tricks to populate the data into 2 tables. At the moment I am just executing each of the INSERT statements twice (there could be multiple insert statements to each table - I am adding 1000 rows at a time). This isn't too bad. I did try inserting to the first table conventionally, and then inserting the the seconds using a SELECT of the first table for the values but this appeared to be slower.

 

Any ideas (short of rewriting a large system to eliminate the derivation of the manipulated ids, which I would dearly love to do but can't justify the massive amount of work it would entail)?

 

All the best

 

Keith

[mikosiko]

the table need to be temporary?

[The Little Guy]

insert 1 time

 

then

 

create temporary table myTemp2 select * from myTemp1;

 

This will then duplicate the table, BUT it won't create any keys you have on the temp table.

 

Or like mikosiko said make it a real table, then delete when done.

[cpd]

Are you saying creating two temporary tables and joining them onto a query is faster than using something like

 

INSERT INTO tbl1 (Col1, Col2, Col3) SELECT (Col1, Col2, Col3) FROM tbl2

 

I'm shocked if joining two temporary tables is faster than that.

[kickstart]

the table need to be temporary?

 

Possible I could create a single non temp table, although I am a little averse to creating temporary things on a live database.

 

insert 1 time

 

then

 

create temporary table myTemp2 select * from myTemp1;

 

This will then duplicate the table, BUT it won't create any keys you have on the temp table.

 

Interesting, hadn't thought of being able to do the create from a select. Thank you.

 

Are you saying creating two temporary tables and joining them onto a query is faster than using something like

 

INSERT INTO tbl1 (Col1, Col2, Col3) SELECT (Col1, Col2, Col3) FROM tbl2

 

I'm shocked if joining two temporary tables is faster than that.

 

Sorry, I possibly mislead you. I needed 2 copies of the same temp table (used in different subselects of a single large select). Populating them both using a conventional insert statements was faster than populating one and then populating the other using INSERT...... SELECT.... syntax.

 

All the best

 

Keith

[fenway]

When you say "efficiently", do you mean speed alone?