Re: create index on a very large table problem

[Chandrakumar Muthaiah]

We are also having the same problem for 204m records. I have no clue on what is going on here.


But the procedure described here helps speed up the index creation.

Load the data with mysql with indices being turned off and then do myisamchk -r on that table
After loading the data into table move the table into another directory or location then do myisamchk -r.
also don't forget to flush the table before moving.

We sometime use another strategy using myisamchk with -k option to disable some of the keys while loading the data and once it is loaded then we use myisam -r <table name> to repair the table. this usually does pretty good job. But takes less time than mysqld itself. at least the time is cut into half. instead of taking 18hours it takes less than 7 hours to build multiple-multi-column indices.

HTH