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

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

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

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.