You could do that. You must also create a blank T1.MYI. Even then, MySQL will still go through its death-defying series of copies and index rebuilds anyway.
You could look into the myisam_repair_threads system variable. Current MySQL installations have it set to 1. If this is greater than 1, MySQL will rebuild all indexes in parallel by assigning an index to be rebuilt on a dedicated thread. I think this feature is currently in beta in MySQL 5.1. The suggestion I made is in probability the best way to handle your large table. It is, therefore, imperative that you tune your server to accommodate this task. Perhaps you should examine the system variable 'bulk_insert_buffer_size' (this was called 'myisam_bulk_insert_tree_size' in MySQL 4). This variable governs the amount of cache per index repair to use for three kinds of bulk inserts. Here are the three types: 1) LOAD DATA INFILE ... 2) INSERT INTO T1 VALUES (...),(...) ... (...); 3) INSERT INTO T1 SELECT * FROM T; In the suggestion I made, I recommended bulk insert type #3 which is step 3. The default value for 'bulk_insert_buffer_size' is 8388608 (8M). Try raising this value to 32M, 64M, 128M, or even 256M and see what happens. I hope this helps. ----- Original Message ----- From: bowen <[EMAIL PROTECTED]> To: mysql@lists.mysql.com Sent: Tuesday, October 10, 2006 9:33:36 PM GMT-0500 US/Eastern Subject: Re: Why does mysql drop index very very slow in a large table? > > 1) create table T1 like T; > This creates an empty table T1 with indexes ndx1,ndx2,ndx3 and ndx4. > 2) alter table T1 drop index ndx3; > This drops index ndx3 on the empty T1, which should be instantaneous. > 3) insert into T1 select * from T; > This will populate table T and load all three(3) indexes for T1 in one pass. > Insert millions of rows into table should be very slow, and obviously be not efficient. Can I use `cp -f T.MYD T1.MYD` instead ? > 4) drop table table T; > 5) alter table T1 rename to T; > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
