Most alter table operations in 5.0 will rebuild the entire table. The
best thing to increase for alter table speed in innodb is the buffer
pool. For more details on how innodb handles alter table see
http://ebergen.net/wordpress/2007/05/07/how-alter-table-locks-tables-and-handles-transactions/
On
>From what I have read, ALTER TABLE to add an index causes the entire
table to be duplicated, so wouldn't my ALTER TABLE command be
duplicating the work done by the SELECT command?
On Wed, Aug 18, 2010 at 4:50 PM, mos wrote:
> At 02:52 PM 8/18/2010, Xn Nooby wrote:
>>
>> Below is a generic vers
It appears the ALTER TABLE starts off quick, and then slows down. I
feel like the indices are larger than allocated RAM, and the system is
slowing down because it is busy swapping out to disk. Is there an
InnoDB specific buffer than can help this? The "sort_buffer_size"
apparently is only for IS
Hi Mike, my comments are below:
On Wed, Aug 18, 2010 at 4:50 PM, mos wrote:
> At 02:52 PM 8/18/2010, Xn Nooby wrote:
>>
>> Below is a generic version of the code I am trying. It does copy the
>> rows very quickly, but I will have to test to see how quickly the
>> indices are built. Is the below
At 02:52 PM 8/18/2010, Xn Nooby wrote:
Below is a generic version of the code I am trying. It does copy the
rows very quickly, but I will have to test to see how quickly the
indices are built. Is the below code what you were suggesting? I had
a little trouble dropping and later adding the prim
Below is a generic version of the code I am trying. It does copy the
rows very quickly, but I will have to test to see how quickly the
indices are built. Is the below code what you were suggesting? I had
a little trouble dropping and later adding the primary index, but I
think I got it figured o
Nooby [mailto:xno...@gmail.com]
Sent: Wednesday, August 18, 2010 9:34 AM
To: mysql@lists.mysql.com
Subject: Slow ALTER TABLE on 70M row InnoDB table
I have been trying to speed up an ALTER TABLE command that adds a
column to a large InnoDB table of about 80M rows.
I have found and tried many
At 10:34 AM 8/18/2010, Xn Nooby wrote:
minutes to dump the 70M rows. However, it takes the LOAD FILE command
13 hours to import the CSV file. My
understanding of LOAD FILE was that it was already optimized to load
the data, then build the indices afterwords. I
don't understand why it takes so
I have been trying to speed up an ALTER TABLE command that adds a
column to a large InnoDB table of about 80M rows.
I have found and tried many different methods, but they are all slow.I
have tried both optimizing the ALTER TABLE
command, and dumping and loading the table (in both SQL and CSV
for
