MySQL Forums :: MyISAM :: How to enable index fast in MyISAM table


Advanced Search

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 2355 Gejun Shen 03/04/2014 07:26PM
Re: How to enable index fast in MyISAM table 1248 Rick James 03/08/2014 12:42PM
Re: How to enable index fast in MyISAM table 1396 Gejun Shen 03/13/2014 10:49PM
Re: How to enable index fast in MyISAM table 1228 Rick James 03/14/2014 06:32PM
Re: How to enable index fast in MyISAM table 1129 Gejun Shen 03/17/2014 01:38AM
Re: How to enable index fast in MyISAM table 1123 Rick James 03/20/2014 06:33PM
Re: How to enable index fast in MyISAM table 1254 Gejun Shen 03/22/2014 07:23PM
Re: How to enable index fast in MyISAM table 1201 Rick James 03/23/2014 02:36PM
Re: How to enable index fast in MyISAM table 1131 Gejun Shen 03/24/2014 12:16AM
Re: How to enable index fast in MyISAM table 1177 Rick James 03/24/2014 10:49PM
Re: How to enable index fast in MyISAM table 1191 Gejun Shen 03/25/2014 08:39PM
Re: How to enable index fast in MyISAM table 1373 Rick James 03/26/2014 11:55AM


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.