MySQL Forums
Forum List  »  MyISAM

How to enable index fast in MyISAM table
Posted by: Gejun Shen
Date: March 04, 2014 07:26PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
How to enable index fast in MyISAM table
2569
March 04, 2014 07:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.