By Sufyan bin Uzayr, noupe – September 06, 2012 at 07:59AM
 |
It’s only SQL, but we like it. This quote, slightly differently used by The Rolling Stones in 1974, is supposed to help you through the hard times. These will occur sooner or later. Unless you’re working with a flat-file CMS such as FlatPress, the database forms the most important part of the Content Management System. And this database can get corrupted, eeeek. WordPress, too, is no exception to this rule. WP uses its database to store and retrieve information as and when needed. The following article gives you a crash course knowledge of the WordPress data storage.
WordPress Database: Getting Started
An average user will probably never need to fiddle with the WordPress database. However, if you are planning to become an ‘expert’ of WordPress, you need to have a proper understanding of the database and its functioning. Furthermore, often database tweaks are required to customize WordPress and/or backup or restore a website. For instance, changing login credentials, or even removing a faulty plugin or theme that may have rendered your admin panel unstable — all of this can be performed by accessing the database. Therefore, it becomes obvious that the database is the most crucial component of your WordPress installation. In this article, we shall be taking a look at the WordPress database in detail.
Basically, we shall begin with an overview of the WP database — its structure, the major tables, taxonomies, and so on. We shall also be covering certain common queries to manage and optimize the database, in the later part of the article. Thus, all in all, this article shall not only help you get a bird’s eye view of your WP database and figure out what-lies-where, but also explain certain steps and methods that can help you keep your database functioning as smooth as a well-oiled machine.
Before going any further, it must be noted that you should tweak or modify your database only after you have made a proper backup, and you are aware of what you are doing.
Never forget: INCORRECTLY EDITING YOUR DATABASE CAN RENDER YOUR WEBSITE USELESS.
wp-config.php And WP Database
When you open the wp-config.php file of your WordPress installation, you’ll notice that the database part contains lines that look something like this:
Most of the information contained here is self-explanatory — you have your MySQL username, password, hostname and so on. The character set is UTF-8, and you do not need to change it, but if your blog is in a language or script that requires special encoding, you’ll need to modify the character set.
Following that, if you scroll down the wp-config.php file, you will notice the value for the table prefix:
As you can judge from the comment in the file, you can keep multiple installations by giving each a unique table prefix. By default, it is set to wp_ However, of reasons of security, it makes good sense to change the table prefix from wp_ to some other prefix of your choice, so as to deter potential hackers from breaking into your database by guessing the table prefix.
The WP Database Structure
Given below is a screenshot of what a typical WordPress database looks like (using phpMyAdmin):
Taking wp-lockdown and wp_login_fails out of consideration, we have 11 tables in the entire database. Let’s retain taxonomy related tables for later discussion, and take a look at each of the remaining tables.
wp_commentmeta and wp_comments
Both of these tables contain data pertaining to comments. Comment-meta refers to meta data about each comment, such as the commenting user’s details, etc. In the admin panel, the corresponding relevant area is the Comments’ section.
wp_links
This table deals with the links, which can be accessed from the Links menu in the admin panel.
wp_postmeta
As the name suggests, this table contains meta data about posts and articles on your website. Often, certain plugins may add fields to this table. The relevant admin panel section is the Posts page.
wp_usermeta and wp_users
These two tables contain information about the user accounts associated with your WordPress website, and the meta data related to the same. The corresponding admin panel section is Users.
wp_posts
As with any blogging software, the core of a WordPress website consists of the posts and articles. The wp_posts table deals with posts. Plus, pages and even navigation menu items are stored in this table. Thus, the corresponding admin panel sections include Posts, Pages and Menus.
wp_options
WordPress stores its configuration settings in wp_options. Basically, all that you can do from the Settings section of the admin panel comes right to wp_options (along side certain settings and configuration tweaks from Appearance menu, such as Widgets).
Taxonomies and Terms
WordPress has three tables to handle custom taxonomies and terms: wp_terms, wp_term_relationship and wp_term_taxonomy.
So, what exactly is a ‘term’? Basically, WordPress treats ‘terms’ as entities — categories, tags, etc. are all ‘terms’. Thus, Uncategorized, the default category, is a ‘term’.
And where do all these terms go? Correct! Terms are stored in the wp_terms table.
Similarly, the wp_term_taxonomy table contains the taxonomies for the terms in wp_terms table. Basically, a taxonomy is a way to group things together. And lastly, the wp_term_relationship table relates a post, page or link with the associated terms in the wp_terms table.
So far, we have familiarized ourselves with the WordPress database structure, the tables contained in the WP database and the role of each table. Further theoretical information can be had from the WordPress Codex itself.
Working With The WP Database
We shall now turn our attention to performing some common tasks on the database. We will be by-passing tasks such as working with the SQL database, inserting and updating data, getting rows and columns, and other similar functions as these have been discussed on the internet a zillion times — you have the WordPress Codex, and you also have Smashing Magazine’s own version of it. Interactions with the database apart, let’s take a look at ways in which we can accomplish certain common admin-level tasks using the WordPress database, instead of admin panel.
Optimize the Database
As you work on your blog, your database transactions pile up overheads which can be a performance hog. To clear such overheads and optimize your database, use this query:
OPTIMIZE TABLE ‘wp_posts’;
Transfer Posts From One User to Another
If you wish to transfer posts and articles attributed to one user account to another account, you can use the following query (you’ll need the usernames of both the accounts):
UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
Bulk Delete Comments
You can use this query to delete all unapproved comments.
DELETE FROM wp_comments WHERE comment_approved=’0’;
Disable All Plugins
Perhaps you’ve accidently activated too many plugins? Or maybe you wish to make certain changes to your website and would like to disable the plugins? You can use the following query to disable all the plugins on your WP website:
UPDATE wp_options SET option_value=’ ‘ WHERE option_name=’active_plugins’;
Disable Comments on Older Posts
If you wish to close comments on posts older than a specific date (say, May 5th 2012), use the following query:
UPDATE wp_posts SET comment_status=’closed’ WHERE post_date<’2012-05-05′ AND post_status=’publish’;
Change Username
If you wish to change the username of a given user (say, ‘abcdef’), use this query:
UPDATE wp_users SET user_login=’New-username’ WHERE user_login=’abcdef’;
Conclusion
With this, we come to the end of this article about the WordPress database. Just in case you do not wish to get your hands dirty, you can consider trying database management plugins such as WP-DBManager or WP-Optimize. In any case, whether you ever employ SQL queries or not, it always helps to know your way around the database.
Got any database knowledge of your own? Feel free to share with us in the comments below!
(dpe)