Your WordPress site has a unique database that you can access via your hosting control panel. You may need to interact with that database at some point. For instance, you might have to use MySQL to delete a table. However, you might have no idea how to do this.
To successfully delete a table, you’ll need to know how to access your WordPress database and have an understanding of what each component does. Fortunately, this is relatively simple as most sites use similar tables.
In this article, we’ll talk about what it means to drop a table in MySQL and when you might need to do it. We’ll also show you how to use MySQL to delete a table. Let’s get started!
Check Out Our Video Guide to Using MySQL to Delete a Table
What Does “Drop a Table” in MySQL Mean?
“Dropping a table” in MySQL refers to the SQL command that enables you to delete a table within a database. When you delete a table, all of its rows will disappear from the database:
It’s a well-known fact among developers that deleting tables in a database can be a risky process. For dynamic websites, such as those built with WordPress, deleting the wrong table can break the entire site.
This is because WordPress constantly makes requests to the database to pull the data that it shows to users. If you accidentally delete a critical table, you’ll need to restore the entire database (or website) from a backup.
Why Delete a Table in MySQL
There are a few situations where you might need to use MySQL to delete a table. If you’re using WordPress, you’ll be uninstalling plugins at some point. Some plugins leave behind “orphaned” database tables. These are tables that contain unused data.
Some plugins do this so you can retain configured settings if you decide to re-install them. Other plugins simply leave tables behind due to poor coding practices.
Unused tables take up storage space. They can also slow down request processing times within your website, and cause compatibility issues with other plugins.
In practice, you shouldn’t notice database bloat unless you’re using a poorly optimized web host. However, it’s still important to clean up unused data in your WordPress site.
This tool enables you to find orphaned tables and duplicate entries within the database and delete them. This should help speed up your site.
However, using MySQL to delete tables might offer much better results, especially if you’re on a shared hosting plan. If you’re using managed hosting options such as Kinsta, you don’t have to worry about database maintenance. However, knowing how to access the database and remove unused tables can help you troubleshoot technical issues with your website.
What To Do Before Deleting a Table in MySQL
Deleting a table in MySQL can have a big impact on your website, especially if it contains important data. If WordPress can’t find those tables, parts of your site might cease to work altogether. Even worse, you might lose important user information in the process.
If you need to delete a database table, it’s recommended that you back up the entire database first. Depending on your web host, you might have access to automatic backups. Kinsta, for example, backs up your website in full every day:
You can also back up your site’s database separately from your WordPress site files. To do so, you’ll need to access the database using a tool such as phpMyAdmin. Most web hosts will enable you to access phpMyAdmin from your hosting control panel.
If you use Kinsta, you can access the MyKinsta dashboard and navigate to your site’s Info tab. Inside, you’ll find a section that reads Database access. Click on the button that says Open phpMyAdmin:
Then, log into your phpMyAdmin account and use the menu to the left to find the database that you want to back up. If you’re only running a single WordPress website, there should only be one database on the list.
After selecting the database, click on the Export button at the top of the page. The next page will ask you what export method and file formats you want to use. You’ll need to select Quick and SQL to download a full database backup in SQL format:
The database backup will download to your computer. If you run into an error after deleting a table in MySQL and you need to restore the database, return to phpMyAdmin and jump to the Import tab.
Within the Import tab, you’ll find the option to select a database file in SQL format. Locate the file on your computer and click on Go to upload it:
Importing a database file will override the existing tables in the database. In other words, it will restore the database to the state it was in before you deleted any tables.
How To Use MySQL to Delete a Table (2 Methods)
There are two ways that you can use MySQL for deleting tables if you have access to phpMyAdmin. Let’s take a look at each method.
1. Delete a Single Table in MySQL
If you’re using phpMyAdmin, you can delete tables using SQL commands or the tool’s graphic interface. For the visual approach, select the database that you want to edit. A list of tables will appear on the screen to the right:
You can select any table by ticking the checkbox to the left of its name. After selecting a table, use the menu at the bottom of the screen and click on the DROP option:
Keep in mind that tables will vary from one website to another. A lot of WordPress plugins add several new tables to the database, so it’s important that you’re sure about what elements to delete.
To drop a table with SQL commands, click on the SQL tab at the top of the screen. On this page, you’ll see a field where you can enter SQL queries. This is the query that you need to use to drop a table:
DROP TABLE tablename;
The tablename variable should be replaced with the name of the table that you want to drop. For example, if you want to drop the wp_users table, that query would read:
DROP TABLE wp_users;
If you enter the table name correctly, phpMyAdmin will delete the element and all of the rows that it contains. We recommend checking your website to see if everything is still working as it should.
2. Delete Multiple Tables in MySQL
Deleting multiple tables in MySQL works very similarly to the first method. With the visual approach, you can select multiple tables in phpMyAdmin, then choose the DROP option from the dropdown menu at the bottom of the screen:
If you prefer to delete tables using SQL, navigate to the SQL tab. You can use the same SQL query for deleting single tables. The only difference is that you’ll need to include multiple table names, separated by commas:
DROP TABLE tablename1, tablename2;
You can add as many tables as you want, but make sure that the names are correct. When you’re ready, click on the GO button to execute the query.
Since you’re deleting multiple tables at once, you need to be absolutely certain that they don’t include critical data. Deleting multiple tables can be risky, but if you have a full database backup you should be able to restore your content.
What To Do If You Want to Delete a Row From a Table in MySQL
In some cases, you might simply need to delete a single row from the database instead of an entire table. To do this, access the database using phpMyAdmin and click on any table.
On the next page, you’ll be able to see all of the rows that the table contains. Here’s an example from the wp_users table on our test website:
As you can see, phpMyAdmin enables you to edit, copy, and delete rows within tables. To delete a row, click on the Delete button and confirm your decision.
Deleting tables in MySQL is simple. You can use phpMyAdmin to select which tables to drop. Alternatively, you can use SQL queries to delete multiple tables in a matter of seconds. Even if you’re not comfortable using code, the command for dropping tables in MySQL is very straightforward.
We recommend that you only drop a database if you’re sure that you don’t need it. For instance, you might want to delete data left behind by uninstalled plugins.
Our MyKinsta dashboard makes it easy to access your WordPress database and manage your site. Reach out to our sales team to discuss which plan is right for you!