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
What are you using as your primary key on this table? Is an auto_increment
field or something non-sequential? Do you have your secondary indexes in
place while you load the table or are you explicitly disabling them and
re-enabling them afterward?
-Travis
-Original Message-
From: Xn No
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
