Alter table disable keys and unique indexes

MySQL & MariaDB Other MySQL® Questions
1

I had a task to create an indexed table with 180M of rows in the fastest way.

I tried to use default approach:

  1. to create an empty table without indexes
  2. to fill the table using LOAD DATA INFILE
  3. to add required indexes using ALTER TABLE

With default configuration mysql performed quickly the second step (approx 30 minutes), but hanged up on the third. I waited for 30 hours, but without any success.

Then I read about key_buffer_size variable and changed it to 1.5G (machine had 2G of RAM). After that mysql applied primary key for 8 hours. Not very good result, because mssql creates the same index for less than an hour. I think the problem was in slow per-row algorythm mysql uses when creating unique indexes and the final primary key file size - 3G, which didn’t fit into 1.5G of key buffer.

Then I tried to use the following approach:

  1. to create an empty table with indexes
  2. to disable indexes by ALTER TABLE DISABLE KEYS
  3. to fill the table using LOAD DATA INFILE
  4. to enable indexes by ALTER TABLE ENABLE KEYS

But this approach didn’t work too, becasue mysql doesn’t disable primary key and unique indexes usage after performing
ALTER TABLE DISABLE KEYS

Can anybody answer me the following questions:

  1. why mysql doesn’t disable unique indexes on ALTER TABLE DISABLE KEYS query?
  2. why mysql uses slow per-row algorithm when creating unique indexes, while it uses fast filesort algroithm when creating non-unique indexes on ALTER TABLE ADD [UNIQUE INDEX | PRIMARY KEY] query?
  3. is there any non-tricky workaround for this problem? I’ve read Peter’s article here [URL]http://peter-zaitsev.livejournal.com/11772.html[/URL] , so it doesn’t fit me.
2

You’re right. If index is not built by sort it may be very slow. I’ve seen it being over 100 times slower compared to building by sort.

UNIQUE KEYS are not disabled by ALTER TABLE DISABLE KEYS because these are also constraints and data could be inserted which will not allow keys to be enabled. Not to mention certain statements such as REPLACE may start working differently.

I however think it would be great if there is some option to force such behavior - in many applications it is OK to take a risk as you know what you’re doing and it is far better than ugly workarounds.

Also there is really no reason to rebuild unique keys by keycache in standard ALTER TABLE.

3

I worte a patch, based on your article MySQL: Loading large tables with Unique Keys and sent it to mysql developers bugreport & patch. It significantly increases speed of adding unique indexes. Hope, it will be reviewed and commited into mysql sources soon.