If frequent changes happens on a table, it may develop some inefficiencies in its indexes. Fragmentation due to blocks moving around on disk and inaccurate index statistics are the two most common problems you're likely to see. However, one cay easily optimize index data for MyISAM tables.

For reindexing a table we can use OPTIMIZE TABLE command. In doing so, MySQL try reading the records again in the table and reconstruct all of its indexes. This will result in indexes with good statistics.

However, reindexing the table takes lot of time if the table size is huge. During that time, MySQL also has a write lock on the table, so data can't be updated.

For offline analysis you can try myisamchk command-line tool:

$ cd your-database-name

$ myisamchk table-name

You need to make sure that MySQL isn't running when you try this, or you run the risk of corrupting your indexes.

Incase of BDB and InnoDB tables they are less prone to this sort of tuning, which is nice things, because the only ways to reindex them are a bit more time consuming. You need to manually drop and re-create all the indexes, or you have to dump and reload the tables. However, using ANALYZE TABLE on an InnoDB table causes InnoDB to re-sample the data in an attempt to collect better statistics.