How to properly maintain mysql indexes?

MySQL & MariaDB Other MySQL® Questions
1

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 (all tables using InnoDB). 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!

2

What are the equivalents of these actions in MySQL?
Analyze table updates statistics. This is not needed often but depends on how often your data changes. MySQL only cares about cardinality, not skewness.
Alter table recreates the full index.

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?

Not anymore with InnoDB plug-in or with XtraDB. You can drop/create an index while keeping the table.

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?

Start with a good database design, and prune or archive unneeded data. You will love maatkit for archiving and other administrative tasks.