Importance of rebuilding indexes

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
GeXus
Forum Regular
Posts: 631
Joined: Sat Mar 11, 2006 8:59 am

Importance of rebuilding indexes

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Importance of rebuilding indexes

Post 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
(#10850)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Importance of rebuilding indexes

Post 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.
User avatar
Inkyskin
Forum Contributor
Posts: 282
Joined: Mon Nov 19, 2007 10:15 am
Location: UK

Re: Importance of rebuilding indexes

Post 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!
Post Reply