Hi,

I have experience with MSSQL, but am relatively new to MySQL. For the past some time I have been working with mysql, mostly in writing stored procedures and tuning them and a bit in administration.

In MSSQL we do things like 'index rebuild/defragment' and 'update statistics/optimization'. This is done to make sure index fragmentation gets fixed and that the query optimizer has the correct statistics to make proper choices.

What are the equivalents of these actions in MySQL? How similar/different are they from MSSQL? For example, is it true that when we recreate any single index in mysql, the whole table gets re-created, which for large tables could take a very long time?

And generally, what other maintenance tasks are there on the MySQL side to ensure query performance does not degrade with time as the new data is inserted/updated/deleted?

I have not done any maintenance work for the past year. The data size has quadrupled [largest tables ~ 20GB], and stored procedure performance has degraded significantly. I know that if I were not to do any index rebuild and statistics update in MSSQL for 1 year, performance would be unbearable by now. So what should I do on MySQL Side (version 5.0.45-log)?

Please suggest.


Thank you!