Anybody with a website that uses a database to store data has to do a little bit of maintenance once a month or so. The frequency depends on how busy the site is and how much is being written/pruned from the database. I use mainly MySQL. Using phpMyAdmin this is quite easy to do. You can use it to create and maintain users and databases. As well as export and import the database. But I am going to focus on maintenance step that some tend to forget. Running optimize and repair.
Writing and pruning to the same tables will create a bit of overhead over time. Running optimize and repair on these tables will resolve potential issues like sluggish performance and corruption.
MySQL and phpMyAdmin are both free to download and use solutions. And very handy for small to semi-big sites that use a control panel on say something like shared hosting or a vps. I can only recommend shell CLI maintenance for big to very big sites for a guaranteed result.
MySQL is an open source relational database management system. Information in a MySQL database is stored in the form of related tables. MySQL databases are typically used for web application development (often accessed using PHP).
phpMyAdmin is a free and open source tool written in PHP intended to handle the administration of MySQL or MariaDB with the use of a web browser.
Login to your control panel, or directly into phpMyAdmin. From the left select the database you want to run optimize and repair on. The tables will load on the right. Scroll down. There’s a checkbox to select all the tables, once checking it select ‘optimize’ from the dropdown. Give it time to run. The page will refresh with results.
Repeat these steps for ‘repair’.
Optimizing your tables doesn’t defragment the database. But it prevents mysql from looking through fragments in a table. Having the index statistics recomputed helps the MySQL Query Optimizer construct better EXPLAIN plans. And that’s what optimize basically does. It creates a temporary table internally, like the one it checks. Performs a few operations on it and drops the original and renames the temporary one back to the original one.
Repairing your tables helps fix any corruption on the table.
In either situations I strongly recommend to do a backup first of the whole database.