Database Consulting Insight – Icebreaking of WordPress Database

WordPress came in as a simple blogging platform almost a decade ago and has ultimately become one of the most used and popular CMS choices for novice and experts alike. There are many high-end websites being built on this unique platform with the capabilities to incorporate any functional possibilities you can think of.

The major advantage of WordPress for the users is that the installation, configuration, and database creation on WordPress do not require any particular knowledge of coding or the underlying structure. However, sometimes you may find the need for some basic understanding of the database while you are dealing with the need to make it optimized for your purpose.

WordPress Database

When it comes to constructing a database, phpMyAdmin is the first choice of many DBAs. However, we will focus more on the SQL queries here by considering the fact that everyone may not be fond of phpMyAdmin. In fact, even when using phpMyAdmin, you have the option of running the SQL queries also.

You may already know that WordPress primarily uses MySQL database. So, to log on to MySQL at the first point, you can run the following command in the terminal:

mysql -u [user_name] -p -D [database_name]

After logging into the WordPress MySQL database, you can next check the tables, which WordPress creates by using the following commands.

To check the table structure, run the command;

desc [table_name];

Further, in this post, we will see how WordPress creates the tables on a typical fresh installation. New tables get created upon installing every new plugin.

Another important thing to note before getting further is that the table prefix used here is “wp,” which stands for WordPress. While following the suite, make sure that you replace it with WP if you used a different prefix for your tables.

Let’s try to have an overview of the tables in a logical sequence than considering them in alphabetical order.

1. wp_options

This table may store all your WordPress settings as:

• Title
• Tagline
• Timezone

All options a DBA sets in the dashboard get stored into this particular table.

2. wp_users

As we can infer from its name, ‘wp_users’ stores all registered users on your site. It consists of all the essential demographics of the users such as:

• Log-in ID
• Encrypted password
• E-mail ID
• Contact numbers
• Registration date and time
• Display name
• Status
• Activation key

3. wp_usermeta

wp_usermeta is used to store metadata (simply data about the data) of all the users. For example, the ‘last_name’ of a particular user gets saved in the ‘wp_usermeta’ table instead of being saved to the ‘wp_users’ table.

There are two distinct fields in the table which you must know about as:

• ‘meta_key.’
• ‘meta_value.’

4. wp_posts

It is to store all the posts on your WordPress website and the data related to it. All posts and pages and revisions are made available in ‘wp_posts’ table. It is also used to store the navigation menu. Various entries such as pages, posts, menu items, and revisions are segregated by the ‘post_type’ column in this particular table.

5. wp_postmeta

‘wp_postmeta’ is simply like user metadata table itself, which contains the data about the data of posts. Database consulting services mostly make use of an SEO plugin, in which all the meta tag data generated from the posts get stored in this table.

6. wp_terms

Tags and categories for the posts, links, and pages are stored in the table named wp_terms. The column which contains this particular table is called a ‘slug,’ which is primarily a string that identifies the terms used in the URL of the website for the duration. It ultimately helps boost the SEO impact as the Google crawlers search for the URL search terms for optimization purpose.

7. wp_term_relationships

wp_term_relationship helps to link the wp_terms to specific objects such as relevant pages, links, or posts. It serves as a relationship map between these objects and terms.

8. wp_term_taxonomy

Taxonomy helps to describe all the terms in detail. As we discussed above, it is the metadata (data about the data) of terms. It adds more simplicity as just a plugin cannot add the needed custom values to the table.

9. wp_comments

wp_comments help to store the comments on your posts and pages of the WordPress website. The table also contains unapproved comments and specific information about the users who put in the comments and also a customized nesting of all comments. However, one important thing to note here is that if you use any third-party commenting mechanism like Disqus, the comments will not be stored in the WordPress database, but will be saved in the corresponding servers of that particular application.

10. wp_commentmeta

Just like all other metadata tables, wp_commentmeta consists of metadata about the comments.

11. wp_links

It is a table which consists of all information about the custom links, which are added to your WordPress site. The Links Manager plugin can be used to enable the deprecated links.

A few quick tricks

Now, by knowing the above, next let us identify a couple of tricks, which DBAs can use to act smartly.

• Change the default username

You may know how to create a username, but WordPress does not let you change it later. However, you can now do it through SQL by running the below command.

UPDATE wp_users SET user_login = '[new_username]' WHERE user_login = '[old_username]';

• Change the password

If you forget your password as WordPress admin, you can now easily change if you have access to the DB by running the command as:

UPDATE wp_users SET user_pass = MD5('[new_password]') WHERE user_login = '[username]';

For this, we must use MD5 as the passwords are usually stored as encrypted.

• Delete all spam comments

Sometimes, spam comments may be your major problem. You can make use of tools like Akismet to monitor and control spams. However, if you identify many spams that need to be removed, you can use the below SQL queries.

Delete the spam

DELETE FROM wp_comments WHERE comment_approved = 'spam'; 

Check the spam source

SELECT comment_author_IP as ip_address, COUNT(*) AS count
FROM wp_comments
WHERE comment_approved = 'spam'
GROUP BY ip_address
ORDER BY count DESC

In conclusion, we hope that this short excerpt helped by acting as an icebreaking session for the WordPress DBAs who were confused about handling databases. Even though there are plenty of plugins coming out nowadays, you should not simply try all of them as and when you come across one, but think twice and find the easiest and most viable solutions for your requirements.

Author Bio:

Sujain Thomas is an expert database consultant with a post-graduate diploma from MIT. She frequently posts on the tech blogs and forums to clarify the doubts of database consulting freshers and experts.