Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
June 30, 2006, 10:45 pm
rate this thread
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.
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
- » select using LIKE where first character is a integer
- — Next thread in » MySQL Database Forum
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum