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

[dtecmeister]

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.

[pinguinu]

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