enable keys, force sort, not keycache

[P R]

ok, Ive googled this alot & cant find something that definitively works...
please help..

problem:
relatively large myisam db table (14million+ rows, approx 4GB on disk), cant seem to force 'repair with filesort' after enabling keys...
mysql 5.08 on linux...

method:
1) create empty table, specifying:
--> data directory='/some-path/with-over-150GB/free-space'
--> index directory='/some-other-path/with-over-150GB/free-space'
--> 2 indexes (neither unique)
----> 1 regular 3-column index, on 1 int(11) & 2 double columns
----> 1 spatial index on a 'point' column
2) 'alter table `x` disable keys;'
3) execute multiple 'LOAD DATA INFILE...' statements to load the data from different sources, 14,205,000 rows
4) 'alter table `x` enable keys;'

...and...
mysql *insists* on performing a (very slow) 'repair with keycache'

I have:
1) assured that I have plenty of disk space in my index & db directories
2) changed the tmpdir variable to another disk with over 200GB free space
3) pretty much 'maxed out' the relevant system variables:
bulk_insert_buffer_size=500M
tmp_table_size=150G
max_heap_table_size=3G
key_buffer_size=500M
sort_buffer_size=3G
join_buffer_size=500M
table_cache=150
myisam_sort_buffer_size=3G
myisam_max_sort_file_size=160G
myisam_repair_threads=4
-I increased these values almost to their practical limits pretty much out of pure desperation...nothing else was working...

I have 4GB RAM & alot of disk space (nothing else consuming memory on the box).
can someone please help me understand WHY mysql simply REFUSES to use filesort when rebuilding the indexes after my 'enabling keys' statement???
I know that I can resort to using using 'myisamchk -n', but that's a hacked work-around IMHO...
according to the docs & everything I've read, adjusting the system variables *SHOULD* cause mysql to preferably choose the filesort method over the keycache method...

so, what gives???



Edited 6 time(s). Last edit at 08/24/2009 08:12PM by P R.