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?
I seem to be left with two options here: a) Do I shoot myself in the left foot right away, b) or Do I wait and shoot myself in the right foot? <bg>
Is there a 3rd option?
TIA
Mike
mos <[EMAIL PROTECTED]> wrote: > I have a 50g CSV file that I am trying to import into an empty MyISAM > table. It appears to go fine except after 10 hours it hasn't completed. A > "Show Process List" shows Info="load data infile ..." and State="Repair > with keycache". The table has a few hundred million rows of data. I assume > it is using "Repair with keycache" to rebuild the indexes after the data > has been loaded. > > From what I've read "Repair with keycache" is going to take a huge amount > of time to complete. How do I disable "Repair with keycache" and use > "Repair with sort" instead (which should be faster, right?)? I'm using > MySQL 4.1.10 on Win XP and 1gb ram. > > TIA > > Mike > >
-- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
