kickstart Posted May 4, 2012 Share Posted May 4, 2012 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 Quote Link to comment Share on other sites More sharing options...
mikosiko Posted May 4, 2012 Share Posted May 4, 2012 the table need to be temporary? Quote Link to comment Share on other sites More sharing options...
The Little Guy Posted May 4, 2012 Share Posted May 4, 2012 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. Quote Link to comment Share on other sites More sharing options...
cpd Posted May 4, 2012 Share Posted May 4, 2012 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. Quote Link to comment Share on other sites More sharing options...
kickstart Posted May 9, 2012 Author Share Posted May 9, 2012 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 Quote Link to comment Share on other sites More sharing options...
fenway Posted May 12, 2012 Share Posted May 12, 2012 When you say "efficiently", do you mean speed alone? Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.