September 27th, 2004, 07:07 AM
Slow to add an index
Perhaps if i rephrase the questiion ...
When I use the alter table ... add index four times on a large table (1,000,000 records) It takes ages (4 - 8 minutes for each index).
What can I do to speed the add index up?
Last edited by howard.cole; September 28th, 2004 at 04:55 AM.
Reason: No reply to original question
September 28th, 2004, 07:51 AM
The problem with the alter table ... add index command is that it seems to create a full copy of the table and then populate the table then rename the table. So...
If I put all the new indexes into one alter table command, it works much faster than multiple alter table commands!
alter table t add index index1(col1);
alter table t add index index2(col2);
alter table t add index index3(col3);
alter table t add index index4(col2, col3);
I shall also experiment with the mysql variable myisam_sort_buffer_size and see if that improves speed further. I shall post any results here
alter table t add index index1(col1),
add index index2(col2),
add index index3(col3),
add index index4(col2, col3);
September 28th, 2004, 08:26 AM
For anyone interested, the doubling of the myisam_sort_buffer_size to 16M made little or no difference to the speed of the alter table command. It still takes 5 minutes.
September 28th, 2004, 05:26 PM
Generally you will want to set myisam_sort_buffer_size as large as possible. Like if you have a gig of ram, you might set it to 800MB before adding the indexes. You can do this on a per-session basis, such as:
Keep in mind that you still don't want to swap out, but it should generally be large. The buffer is only used when REPAIR'ing or ALTER'ing or bulk loading data, so it will only be allocated occasionally.
SET SESSION myisam_sort_buffer_size = 800*1024*1024;
ALTER TABLE foo ADD INDEX ...;
The last thing to look at, when you are doing the actual ALTER TABLE, look in the SHOW PROCESSLIST; and see if it saying something like: Repair by sort; or Repair by keycache. myisam_sort_buffer_size only matters for the Repair by sort, not for the keycache one.
September 29th, 2004, 07:28 AM
Thanks HarrisonF, I set the session sort buffer size to approx 70% of memory size, and it speeded the alter command from 5.5 mins to approximately 5.0 mins. Looking at the output from the process list the following occurs:
160 seconds : Copy to tmp table
140 seconds : Repair by sort
~1 second: rename table
The major part of the query is therefore the copy to tmp table. Can this be speeded up somehow?
September 29th, 2004, 12:25 PM
What do you get if you look at the OS system load during index creation?
Are you running windows or *nix?
How large is your table?
Could it be about 1.4GB?
A small test on my workstation with a 800GB file gives me a copy time in the vicinity of 65-75 seconds. Depending on if I copy to another disk or the same disk. This sums up to somewhere round 10-12MB/sec which is fairly average on normal IDE disks.
If that is the case then the problem currently is in the disk speed so unless you want to speed up your disks you can add more RAM to the system to be used as file cache. But if you have this size of files then the OS usually wants to write a lot to disk anyway.
If you are using InnoDB as source. Why not use InnoDB for this table also. I'm not sure which procedure that InnoDB uses to create their indexes but it might be faster than the MyISAM.
Or you can wait and read the advice from HarrisonF he usually knows what he is talking about
September 29th, 2004, 11:26 PM
InnoDB is actually slower for creating indexes than MyISAM. MyISAM has many optimizations that it does for bulk loading.
With that said, how big is this table? How fast do you expect it to take? When you started, it took about 30 minutes to add the indexes, and now you do them all in ~5? Adding 4 indexes isn't the fastest thing in the world. You didn't mention the hardware you are doing this on. If you have more than one CPU, you can set myisam_repair_threads to however many CPU's you have. That way it will actually create multiple indexes all at the same time, which then can go across multiple CPUs.
You could test this out even if you have just a single CPU, but I doubt you would gain much, since you are CPU/memory bound for creating the indexes.
One last idea, is that you can create the table with the indexes already there. But then disable them with something like:
Then load the data into the table thru your normal means.
ALTER TABLE foo DISABLE KEYS;
Then finally turn the indexes back on, which causes MySQL to build them in a batch.
This should avoid the entire Copy to tmp table; part in theory, which should cut that 160 seconds off. Let me know if that works.
ALTER TABLE foo ENABLE KEYS;
September 30th, 2004, 06:14 AM
Great thinking HarrisonF, got the times down to approximately 2.5 minutes using the disable keys method, presumably by not creating a temporary table.
So... optimised the index creation from 30 to 2.5 minutes. Not bad!
Many thanks to sr and especially HarrisonF.
January 6th, 2006, 01:35 PM
Why is drop index so slow?
I am having a similar issue when I attempt to drop an existing index from an INNODB database. I am curious why droping takes so long since it doesn't have to rebuild any indexes.
January 25th, 2006, 06:06 PM
If things have not changed in InnoDB tables since I last looked then this is probably the same issue. Although the indexes are not rebuilt, you will probably find that a table copy still occurs - which is what is taking the time. You can check this by monitoring what happens during the DROP.
Originally Posted by compavalanche
If this does turn out to be the case then you can attempt the suggestions used in this thread. i.e disabling the key or tuning the appropriate variables.
Hope this helps.
January 26th, 2006, 01:50 PM
Which index are you trying to drop?
The data in InnoDB tables is stored as leafs on the binary tree which is the primary key. If you are trying to drop the existing primary key InnoDB will create a pseudo primary key.
In that case you actually just switching one key for another.