I've posted a similar post in the past -- but there I was mucking around with blank index files and frm files to fool myisamchk into repairing a table.
But now I think I've come across a much better and more efficient way to do a REPAIR Table in order to upgrade my database tables from Mysql 4.1.x to 5.5.8. All this comes from the fact that REPAIR TABLE does not rebuild the table indexes like myisamchk does, which is very unfortunate. Sure, REPAIR TABLE works great for small tables, but if you have any tables of larger size (millions of records or more, with multiple indexes), REPAIR TABLE can take hours or days to do a simple repair/upgrade. And in most cases, applications just can't be down for that long during an upgrade cycle (not everyone runs a huge shop with multiple dev/test/upgrade/production servers). So here is what I have done, and propose this as a better REPAIR TABLE for MYISAM tables (in pseudo code): 1. Retrieve the original CREATE TABLE DDL with "show create table SOURCE" 2. Modify DDL to change the table name to a new target table, let's call it TARGET 3. Execute new DDL to create empty TARGET table 4. Run 'myisamchk -r --keys-used=0 TARGET' (to disable all index keys on new table) 5. flush tables; lock table SOURCE read, TARGET write; 6. insert into TARGET select * From SOURCE; 7. flush tables; unlock tables; 8. 'myisamchk -prqn TARGET' (repair to re-enable all keys, do not modify MYD table, use sorting, in parallel) 9. rename tables to replace SOURCE with TARGET I've written a PHP script to do exactly this, and it works beautifully. My source tables are mysql 4.1.x tables, and the target tables are now fully 5.5 compliant (verified with mysqlcheck --check-upgrade). The best part is that for tables with 50 million short rows, it ran in 7 minutes, and a table with 30 million rows, it ran in 4 minutes. I'm now running it on a table with over 200 million rows, and I expect it to take an hour or so... but in all cases, doing a REPAIR TABLE on any of these large tables would take days to complete. So why can't the REPAIR TABLE command do something like this in the background for large MYISAM tables? -Hank