Page 1 of 1

Importance of rebuilding indexes

Posted: Sat Feb 09, 2008 4:12 pm
by GeXus
I've been struggling with a very slow mysql database for several weeks, I had tried almost everything! I quadrupled memory, bought a new server, tried to optimize queries, created indexes if any were missing where appropriate.... Still nothing would increase the speed. After speaking to a DBA, he said without looking at anything, his best guess would be that I need to rebuild indexes. So, I went to all my tables and did:

Code: Select all

 
REPAIR TABLE table_name QUICK ;
 
And sure enough, the server is now lightening fast!

I just thought I would share this for anyone experiencing similar "unknown" slowness, and I'll follow-up with a question, Has anyone else experienced the same thing and found the same solution? if so, how often do you repair?

Re: Importance of rebuilding indexes

Posted: Sat Feb 09, 2008 4:22 pm
by Christopher
You can read about the REPAIR command and related command line programs here:

http://dev.mysql.com/doc/refman/5.1/en/ ... table.html

Re: Importance of rebuilding indexes

Posted: Sun Feb 10, 2008 1:51 pm
by Benjamin
For those who don't read that:
Caution

It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to filesystem errors.
Warning

If the server dies during a REPAIR TABLE operation, it is essential after restarting it that you immediately execute another REPAIR TABLE statement for the table before performing any other operations on it. (It is always a good idea to start by making a backup.) In the worst case, you might have a new clean index file without information about the data file, and then the next operation you perform could overwrite the data file. This is an unlikely but possible scenario.

Re: Importance of rebuilding indexes

Posted: Mon Feb 11, 2008 2:49 am
by Inkyskin
I have only had to rebuild a set of indexes once - but it saved my behind. We had a database server crash on us after a stupidly heavy load, after rebooting all our indexes had been corrupted. In the end we werent even able to rebuild the main indexs from the existing database, instead we had to use the ones from a backup, and then rebuild those.

Don't always rely on a rebuild to fix them ;) Backups FTW!