problem when creating a secondary index on a large innodb table

March 18th, 2011 by xrigher Leave a reply »

The table has only 2 integer columns (id, friendId) with the combined key (id, friendId). Now I want to reversely search users by friendId. Without index friendId, each search will scan the whole table and takes about 90s. So I decided to create index on friendId.

First, I tried: CREATE INDEX ON TABLE tb_name index_name (column_name). After 30 hours, it’s still not finished and I had no idea when it would finish. So I interrupted it.

Then I truncated the whole table, altered the table to add a new index, and then insert the whole data by LOAD DATA INFILE …. INTO TABLE tb_name. During the procedure, only 5% of cpu was used and io_wait is large. And the final time it took was 55 hours:

mysql> LOAD DATA INFILE ‘/home/weiping/rr.dump/rr.friends.all.20100310.csv’ INTO TABLE friendships;
Query OK, 137138510 rows affected (2 days 7 hours 46 min 5.46 sec)

My my.cnf is like this:

key_buffer              = 768M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 144
query_cache_limit       = 1M
query_cache_size        = 32M
innodb_buffer_pool_size = 1024M

Of course, I have switched off these unnecessary checks:

SET unique_checks=0;
SET sql_log_bin=0;
set foreign_key_checks=0;

I loaded the data before without the secondary index, it took only 30 minutes! Now I’m wondering why it took so long time with one more index…

BTW, will increase the size of sort_buffer will help massive insertion (and with secondary index)?


Leave a Reply