How to enable index fast in MyISAM table

[Gejun Shen]

Hi,

Currently I use mysql 5.1.50 and I have a table contains 1 billion records at the size of about 2T, the index size is around 50G. This table have 5 indexes and 6 partitions partitioned by date range. It's running well normally. However, I disabled the keys by mistakes and now mysql optimization engine can't use the index. The query performance now are unacceptable. I don't want to use "alter table xxx enable keys" because it will take a very long time to finish.

Could anybody advise what I can do now? Is there effecient way to allow optimization engine use index again?

If I have to do enable keys, will changing myisam_repair_threads to a value greater than 1 help on the performance?

What if I migrate this from MyISAM to Innodb? Could Innodb handle that much data effeciently in terms of read/write throughput in case Innodb will store index and data in buffer pool and will occupy more memories and disk(I have no requirement of insertion from multiple threads, but read from multi-threads)?

Thanks



Edited 1 time(s). Last edit at 03/05/2014 01:54AM by Gejun Shen.