How to Optimize WordPress Database Using PhpMyAdmin

WordPress is written with the help of PHP Language as its Scripting Language and for its database management system MySQL is used. To use and manage WordPress we do not need to learn either both of them as one click WordPress install is now supported by almost all the web hosting companies, but still to understand basics of both would be useful for optimizing and troubleshooting WordPress issues manually and to learn how WordPress works.

In this post we will explain how to optimize your WordPress database manually using PhpMyAdmin without help of any expert in between. Here we will explain how to take backups, optimize WordPress database and many more things related to WordPress database.

How WordPress Uses Database to Store and Retrieve Data

WordPress is full of PHP Scripts and it uses PHP (A Programming Language) to store and retrieve data from server’s database. When we install a fresh copy of WordPress in a server the following table gets created by default, they are as follows –

• Comment Meta
• Comments
• Links
• Options
• Post Meta
• Posts
• Terms
• Term Relationships
• Term Taxonomy
• User Meta
• Users

These tables include almost all the data used in WordPress. Whatever we do in WordPress like publish posts, pages and comments, categorize our posts in categories or tags, create users and customize our site for custom permalinks. Such all such things are stored in a database under specific tables.

Understanding WordPress Default Database Tables

When we install WordPress, its installation creates 11 tables by default in the database. Each and every single table contains different information which makes the WordPress function correctly. Looking at the basic structure of such tables you can easily come to know which table includes what part of your information.

Note that WordPress uses a database prefix like wp_ before its individual table’s name. Which can be different even; if you had used any other table prefix term while installing WordPress back.

Below is the information of all the tables and what they store, they are as follows.

wp_commentmeta

It includes all your comments meta information. There are about four sub-tables are inside it as comment_id, meta_id, meta_key and meta_value. When somebody comments on your site it store it first in any of one status like approves, pending, spam and trash and all such things are stored in comment meta.

wp_comments

It includes all the information of comments like name, email id, website and whole comments too. All the comments and conversation are stored in this table.

wp_links

It contains the links which is used in blogrolls in earlier version of WordPress. Today it is not used much as the use of blogrolls is almost zero today. It is least used table in database of WordPress but still used many a time if you use blogrolls in your WordPress based website.

wp_options

In this table most of your administrative information are stored like admin email, website url, the default category time formats and much more informations.

wp_postmeta

It includes your all meta information of your individual posts stored in WordPress. The biggest use of post meta is used in storing your custom post information like post titles, descriptions and keywords.

wp_posts

It includes your entire list of posts whatever you write and publish in WordPress. This is one of the most used and important table which includes your all hardworks.

wp_terms

It includes your categories and tags; WordPress has a powerful feature of arranging the posts according to sections and subsections. For example if you have 2 categories as technology and gadgets then your category terms will be stored inside wp_terms table.

wp_term_relationships

It is the part of wp_terms where wp_term_relationship includes the information about which post belongs to which category. For example ABC post is posted in XYZ category; here wp_term_relationships table will store that ABC post is belonging to XYZ category. It saves the relationship between your posts and categories.

wp_term_taxonomy

WordPress also have good features of Categories and Tags which is clear as discussed above. The wp_term_taxonomy table store data which helps to differentiate the category and tags. In simple it stores what is category and what is tag in the bubbles is huge categories and tags.

wp_usermeta

It includes the meta information of users in WordPress.

wp_users

It includes all the user’s detail and information which is created in WordPress. It stores details like username, name, password, user email address, social links, author bio, etc.

Why to Optimize WordPress Database?

PhpMyAdmin is an open source software which is freely available to download and use over internet. It provides a web based graphical user interface to manage you MySQL Database. Most of web hosting companies provide this software in bundle with their control panel which is also known as cPanel. PhpMyAdmin allows us to manage our database effectively and perform database management tasks.

Note: It is always suggested that before making any changes and editing in core database, backing up it is useful to secure the errors.

To add any additional feature in WordPress we install plugins. We also try or test few plugins too to drive down with the features and functionality of its. Few of such plugins create its own table in WordPress Database which expands the size of your database. If the database gets bigger day by day then it will consume more space and in coming days it will slow down your whole website as it needs to search your whole database every time for every single execution.

Here we need to optimize our database to increase the performance time of web server. The limited size of the database leads to robust performance of a website. So we always need to optimize our database by deleting (dropping) unused tables.

Optimizing WordPress Database Using PhpMyAdmin

You can optimize your database by deleting or dropping your unused tables, here is how to identify and drop your unused tables to optimize your database.

First of all open your cPanel or Control Panel which is provided by your web hosting company.

Under control panel you will see PhpMyAdmin icon there, click on it.

You will be redirected to PhpMyAdmin Screen. Once you see PhpMyAdmin Screen you can go to list of database, export your database and import your database too.

Note: Take a backup of your database before going further…
[Use Export and Import to take backups and restore your database]

On next screen there will be list of database, select the database file.

Once you click on desired database, you will be redirected to next page where you will have option to drop your unused tables.

Here you need to find out what tables are not in use. Once you found all the unused tables just select them and drop them one by one.

After deleting or dropping unused tables one by one check your website by navigating or loading few pages on a new tab and check is everything working fine or not. If everything go smooth then you have done it properly and you have optimized your database. If anything gone wrong just import the deleted table from your backup and retry it.

You will see that the response time of your web server gets faster than before if you were using so many unused database tables before.

This database optimization is useful for bloggers and web masters who love to test new plugins in their site. You can perform a regular or interval based optimization of database also to enhance the speed and performance or response time of your web server.