Hi,
On Mar 3, 2005, at 11:32 AM, mos wrote:
At 10:07 PM 3/2/2005, you wrote:Hello.
You may use ALTER TABLE .. DISABLE KEYS to speed up the loading process
on the MyISAM table.
That may work provided I can get the keys rebuilt later using FileSort and not KeyCache.
You see the problem isn't in loading the data into the table which occurs reasonably fast that because running "Load Data Infile" on an empty table will disable the keys until all the data is loaded, so explicitly disabling before hand them won't help. After the data is loaded, Load Data Infile will then rebuild the keys and will choose to use "Repair with keycache" whereas "Repair with filesort" would be 100x-1000x faster. There doesn't seem to be any way to get it to use "Repair with filesort".
So I could use disable keys as you had suggested and then rebuild them manually with myisamchk "repair with sort" provided it doesn't reload all the data into a temporary table (which Alter Table usually does). If it does create a temporary table it will physically reloads the 500 million rows a second time and I will need another 100g of free disk space.
So is there a solution to this paradox?
It chooses which method to use based on your myisam_sort_buffer_size, myisam_max_sort_file_size and myisam_max_extra_sort_file_size. Have you increased the size of these? Keep in mind these are SESSION variables, so they can be set on the connection right before you LOAD DATA INFILE.
You always have to tell MySQL how much extra memory/disk it can use, it can't assume that you want it to use it. That is why it will use Repair by keycache, since it doesn't use any extra resources. If you increase the amount of diskspace or memory, then it should use a Repair by sort instead.
Also keep in mind that Repair by sort doesn't work for UNIQUE or PRIMARY KEYs.
Regards,
Harrison
-- Harrison C. Fisk, Trainer and Consultant MySQL AB, www.mysql.com
Get a jumpstart on MySQL Cluster -- http://www.mysql.com/consulting/packaged/cluster.html
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
