How much time does MySQL need to build an index of a table with 30,000,000 entries that are strings of length 256? At the moment it seems to take hours and I don't know how long I should wait till I conclude that MySql simply failed at building an index.
A:
it could easily take hours. it all depends on the machine specs, load, etc etc. to see whether it's failed, check something like top or watch your hard drives - if they're going mad it's still indexing.
oedo
2010-04-09 13:59:34
A:
Depending on your OS you may check for disk activity (i.e. does it reads/writes DB files) to find out if it failed or not.
BarsMonster
2010-04-09 13:59:52
+1
A:
You may run SHOW PROCESSLIST \G in mysql console to watch its state. I had a similar problem just a couple of hours ago, but my table was much smaller.
Here a list of thread states you will definitely need. After an hour of waiting I realized that ALTER TABLE CREATE INDEX is in Locked state, I needed to restart mysqld and run the statement once again. That time I had index built in 15 minutes.
By the way, I recommend to run index creation from mysql console, GUI tools may add some spices to the process.
newtover
2010-04-09 16:12:34