Hi all, Can someone tell me how to speed up the index creation???
I am trying to build an index for a 13,875,354 records(13 million) table with 176,322 distinct vendor_id(VARCHAR(40)) in the table. the existing size of the transaction table: transaction.MYD = 2,128,954,624 bytes transaction.MYI = 1,096,510,464 bytes (there are already 5 indexes built in this table) transaciton.frm = 9,450 bytes I dig through the manual and changed any possible settings set-variable = max_heap_table_size=2000M set-variable = key_buffer=2500M set-variable = max_allowed_packet=1M set-variable = table_cache=512 set-variable = sort_buffer=2500M set-variable = join_buffer=2500M set-variable = record_buffer=2500M set-variable = myisam_sort_buffer_size=2500M set-variable = myisam_max_sort_file_size=2500M set-variable = myisam_max_extra_sort_file_size=2500M set-variable = thread_cache=8 We are running mySQL in a 4G memory linux box, I believe the buffer size I allocated to mySQL should cache the whole table. Instead, I am wrong, while building a new index, I saw there was temporary files created for the transaction table (the write disk activities were very source consuming) and the single index creation took 4.5 hours to finished!!!!!!!!!!!!!!!!!!!! Unbelievable Any suggestions will be greatly helpful!!! Lee Zeborg Inc. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
