Take a look at http://lists.mysql.com/mysql/158737 for an interesting 'trick' to optimze index creation.
Basically it amounts to: * Create the data without indexes * Move (rename) the table.MYD file to a backup * Create an empy MYD file using TRUNCATE TABLE * Add indexes * Move the backed up MYD file back to table.MYD * Repair the table using myisamchk -q -r table.MYI See the post for more details. -Avi Hi folks, I've got a problem creating an index on a MYISAM table with 302,000,000 lines, roughly 58 GB on disk. Attached you will find the table definition, output of "mysqladmin variables", and "mysql -e 'show status'". After creating the above-mentioned table, I ran: ALTER TABLE test_table ENABLE KEYS; loaded the data and then ran (and is currently still runing): ALTER TABLE test_table ENABLE KEYS; show processlist reveals; +--------+----------------------+-------------------------------------------+ | Time | State | Info | +--------+----------------------+-------------------------------------------+ | 948878 | Repair with keycache | alter table fd_aetna_trad_clm enable keys | and it's still running! That's roughly 11 days and it's not complete yet! Here's the current file sizes under the mysql root directory... -rw-rw---- 1 mysql mysql 61361175364 Mar 18 00:51 test_table.MYD -rw-rw---- 1 mysql mysql 7320667136 Mar 29 10:07 test_table.MYI -rw-rw---- 1 mysql mysql 10190 Mar 17 21:16 test_table.frm The box has 1GB of memory and a P4 1.6GHz processor with EIDE disks (dma enabled) and no raid. The system is Linux 2.4.21-rc7-openmosix with smp and the filesystem is ext3, running MySQL version 4.0.13-log. So, if any of you out there have time to look at the attached file, I'd greatly appreciate it. I'd like to know when the index creation will potentially end, and also if I can get the index creation to complete in a shorter amount of time, given the memory and cpu specs of the box. Thanks, Jeff ===== J. Horner Software www.jhorner.com 615-347-6899 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
