How to Defragment a Website Database

When you restore a website to a previous point in time, your database will create and store redundant timestamped tables, which will need to be removed. Additional tables can cause bloating, load delays, and an inability to run website backups. Use these instructions to optimize your database.

Note: If you need help accomplishing any of this, create a support ticket with your web host and be as clear as possible with your instructions. If it’s easier for you, send them a link to this blog post.

1. Delete Timestamped Tables.

1. Log into phpMyAdmin.
2. Some hosts provide this interface. If yours does not, you can access it via the command line. Using a dashboard is a lot more efficient because working in the command line requires you to delete the database tables one at a time. Ask your host how to access this area.
   Note: Two people cannot be logged in to phpMyAdmin at the same time; it causes errors.
3. Click on the name of the database in which you want to purge tables.
4. From there, select a directory and delete tables with timestamps.
5. Once this is done, your database will be fragmented with lots of space between tables, which causes delays in call requests, such as when you click Publish button in a WordPress post dashboard.
6. At this point, you will need to defragment your database via an optimization request. To do that, follow the instructions in part two.

In 2023, I purged nearly 90% of the tables in one of my databases, and after it was done, the Publish button in WordPress took 62 seconds to resolve. This is because the call request had to locate destinations across many fragments in the database. When I optimized that database, it brought that time down to just 6 seconds.

2. Optimize Database.

1. Log into phpMyAdmin.
   Some hosts provide this interface. If yours does not, you can access it via the command line. Using a dashboard is a lot more efficient because working in the command line requires you to delete the database tables one at a time. Ask your host how to access this area.
   Note: Two people cannot be logged in to phpMyAdmin at the same time; it causes errors.
2. Click on the database for the associated site.
   The database name can be found in the wp-config file, which is in FTP.
3. In phpMyAdmin, hover your mouse over the column for overhead.
   A list of tables will be sorted by most to least overhead with the most at the top, but not all tables will have overhead. To the left of the table names are checkboxes that you can check off to run optimizations. Check the boxes next any tables with overhead. Scroll to the bottom to find a dropdown list and select the option, ‘Optimize Table.’
4. A prompt will show, “The following tables have been optimized,” and it’ll show the results.