Optimizing database

Yes, the DB should be optimized now and again. You can do it manually through PHPMYADMIN or use a plugin like “WP Sweep“. The advantage of the plugin is that it cleans up unneeded stuff prior to optimization.

There are two available procedures to clean up database tables: OPTIMIZE and ANALYZE. While I’m sure plugins exist, neither procedure should be run from within WordPress due to the potential of the request timing out, which could be disastrous. They should be run from phpMyAdmin or from the command line (SSH).

The OPTIMIZE procedure is only effective with a type of table called MyISAM. If your tables are not MyISAM (ask your host), then you shouldn’t use the OPTIMIZE procedure! If your tables are MyISAM, then OPTIMIZE should be run on a regular basis, perhaps once every two weeks in an external CRON task.

The ANALYZE procedure should be run on all tables on a regular basis because it oftentimes results in faster queries. ANALYZE should also be run on a regular basis, perhaps once every month in an external CRON task.

Finally, be very careful the first time you run these procedures! Since they haven’t been run for seven years, they could take a LONG time to execute. I’d suggest running OPTIMIZE and ANALYZE manually on each individual table. After the two procedures have been run on all your DB tables, they can be run in bulk in external CRON tasks.

It is true that OPTIMIZE should be used for MyISAM Tables and that used to be the default format. InnoDB is now the default format although MyISAM is still used as well. You should be using REPAIR Tables as it will Optimize “and” correct any errors for both, MyISAM & InnoDB Tables.

As dealing with database can sometimes be tricky and you definitely want to avoid mistakes, talk your Hosting Support for further help on this. Get them to also help with setting up a Cron for regular use of REPAIR.

Thanks for the answers sofar. It seems that using a plugin might be the best choice because it does the job more or less for you.

Is WP-Sweep the beste choice? I have seen the reviews and most of the reviews say it is excellent while a few (2 of 100) say this plugin ruin their sites.

I will of cource take backup first, but what is your take on plugins for this job? What is your choice? Which plugin would you use?

I use WP Sweep periodically to clean out unneeded records and optimize the database.

Thanks again for the useful information. I have now installed the plugin and am learning about it before I start optimizing my database.

The positive about this plugin is that I can choose what I sweep.

What I miss is basic practical information about the plugin and how to use it, especially for users that are new to this. I was surprise by the lack of basic practical facts – I had to use Google to find good information about the plugin.

What guide can you reccomend for WP-Sweep?

Thank you

I guess the values for % Of are the ones that I should be looking at, but what does N/A mean? Does it mean that the plugin has not calculated the value or does it mean that this part of WordPress does not need to be sweeped?

If it is the latter, it is strange because I have N/A for my database. So the database that has not been optimized for 7 years is already optimized. Does that make sense? Does the database auto-optimize? What’s going on?