469,603 Members | 2,075 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

INSERT INTO ... SELECT (...) performance questions

VMB
Using version: 4.0.18-Max-log

We're getting some fairly poor performance when doing an INSERT INTO
temp_table ... SELECT (...). The SELECT is grabbing 2K row chunks from
its table, so the problem is not that the query results are so
monstrous that it's just taking that long to gather them all.

The problem appears to be one of IO. When an INSERT...SELECT is run,
the SELECT part of the query is run first and those results saved in a
temporary table. Then the contents of that temporary table are
inserted into its final destination, which in our case *also* happens
to be a temporary table. All of this table reading and writing is
causing things to drag.

Unfortunately I don't have our MySQL variables handy or our hardware
specs. But I can get them in fairly short order either is needed. I
do know that our hardware is < 6 months old and has a few gig of RAM.
The discs, if I recall, are a RAID array but off the top of my head I
don't know which flavor of RAID.

Our questions:

1) Why does MySQL write to a separate temp table when the destination
table is already a temp?
2) Is there some way to make the results of the SELECT write directly
to the destination table (be it temporary or not)?
3) Are there any other suggestions for resolving this performance
problem? Maybe there's a variable which at least will force the SELECT
to write to a TEMPORARY HEAP table instead and avoid the extra disc IO?

Thanks for any assistance,

--V. M. Brasseur
Software/Database Engineer
iPost
http://www.ipost.com

Jun 30 '06 #1
2 6306

VMB wrote:
Using version: 4.0.18-Max-log

We're getting some fairly poor performance when doing an INSERT INTO
temp_table ... SELECT (...). The SELECT is grabbing 2K row chunks from
its table, so the problem is not that the query results are so
monstrous that it's just taking that long to gather them all.

The problem appears to be one of IO. When an INSERT...SELECT is run,
the SELECT part of the query is run first and those results saved in a
temporary table. Then the contents of that temporary table are
inserted into its final destination, which in our case *also* happens
to be a temporary table. All of this table reading and writing is
causing things to drag.

Unfortunately I don't have our MySQL variables handy or our hardware
specs. But I can get them in fairly short order either is needed. I
do know that our hardware is < 6 months old and has a few gig of RAM.
The discs, if I recall, are a RAID array but off the top of my head I
don't know which flavor of RAID.

Our questions:

1) Why does MySQL write to a separate temp table when the destination
table is already a temp?
2) Is there some way to make the results of the SELECT write directly
to the destination table (be it temporary or not)?
3) Are there any other suggestions for resolving this performance
problem? Maybe there's a variable which at least will force the SELECT
to write to a TEMPORARY HEAP table instead and avoid the extra disc IO?

Thanks for any assistance,

--V. M. Brasseur
Software/Database Engineer
iPost
http://www.ipost.com
The extra temp table is probably being created because you have an
inefficient query. You can use "desc select ..." to analyze the query,
but you probably need to add a key or redo the query to utilize any
existing keys.

Jul 2 '06 #2
hy
try this

save in excel format what da yoy want to insert into your table, arange colunms, comma ","
write into first cells : insert into TABLE VALUES , ather tahet in second cells the colums and finnaly );
commit

fill up at down and Finish

cop and paste in SQL window
Jul 6 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by robert | last post: by
11 posts views Thread by Sezai YILMAZ | last post: by
8 posts views Thread by Josué Maldonado | last post: by
8 posts views Thread by nano2k | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.