WordPress uses a database to store, retrieve and display data. This database is the most crucial part of the CMS, and I believe that WordPress is so successful because of how it structures the database. Since its earliest days WordPress adapted a very simplistic approach which is convenient, easy to understand and easy to manage.
WordPress provides developers and users several ways to access data stored in database right from their website. Most users will probably never need to access their database directly. However, In order to get a better grip on WordPress, it is important that WordPress users make themselves familiar with WordPress Database.
By understanding the database structure you can also easily fix some problems. For example, you can change passwords, disable plugins, select themes and do other things, directly from the database without having access to your admin panel.
Important: Always backup your database before making any changes to it.
The Database Part of wp-config.php
When you are setting up a fresh install of WordPress, you are asked to provide database information by either manually editing the wp-config.php or by providing that information during the installation. Here is the database part of wp-config.php
// ** MySQL settings - You can get this info from your web host ** // /** The name of the database for WordPress */ define('DB_NAME', ''); /** MySQL database username */ define('DB_USER', ''); /** MySQL database password */ define('DB_PASSWORD', ''); /** MySQL hostname */ define('DB_HOST', 'localhost'); /** Database Charset to use in creating database tables. */ define('DB_CHARSET', 'utf8'); /** The Database Collate type. Don't change this if in doubt. */ define('DB_COLLATE', ''); /** * WordPress Database Table prefix. * * You can have multiple installations in one database if you give each a unique * prefix. Only numbers, letters, and underscores please! */ $table_prefix = 'wp_';
The database name, database username, and database password parts of the file are self-explanatory. Database Charset is the character set to be used for creating tables. In most cases you don’t need to change it from utf8 to anything else, but if your website will be in some other language and/or you want to use other character set you can do so here.
The table prefix as explained can help you set up multiple installations in one database. For example you can setup the prefix for one installation blog1_ and use blog2_ as prefix for some other WordPress installation.
Security Tip: Changing the table prefix is recommended by WordPress security experts, who suggest that by changing the default table prefix to random combination of letters and numbers one can make it difficult for hackers to crack into database by guessing the table names.
WordPress Database Structure
Once you have setup your database information in wp-config and run installation, WordPress will create tables in your database and populate them with some default data. Your database structure would now look something like this:
Note: We are using phpMyAdmin to manage database. phpMyAdmin provides a web based interface to manage your databases. Most modern web hosts have phpMyAdmin installed in their control panels. Some web hosts particularly dedicated server providers and self-managed hosting service providers may not have any control panel or phpmyadmin. In that case you can still manage your database from mysql console.
Back to database structure… Notice that WordPress has created 11 tables for us. The table names are easy and self-explanatory. But we will talk about them anyway.
The Posts Table: wp_posts
When you write an entry into your WordPres powered website, it is stored in this table. This table has several fields and here is a snapshot showing some of those fields:
I will skip the self explanatory fields, directly to post_type. The pre-defined WordPress post types are post, page, revision, attachment, and nav_menu_item. If you have defined a custom post type then it will appear here as well. Like this:
Another field that might confuse some users is post_mime_type. You will notice that usually it is empty but when there is a post_type attachment then you will notice that it will show the file type like image/jpeg or application/pdf.
The Options Table: wp_options
It is perhaps the most interesting table in a WordPress installation. As it is apparent from the name that wp_options table is where WordPress stores configuration settings for your website. This table has the following fields:
This table contains crucial information such as your site name and URL, site’s admin email, active plugins and themes, etc. These options are very useful in cases when you lose the access to your website’s admin section. Learning more about these options can also allow you to solve some tricky problems in a quicker and more efficient way.
Taxonomies and Terms in Database
WordPress uses three tables to handle taxonomies and custom taxonomies. These are:
This looks quite complicated to many people who are new to databases, but actually it is quite simple and elegant. The first table wp_terms contains all the terms. For example, Blogroll is a term and so is Uncategorized. In a default installation Blogroll is a link-category and Uncategorized is the default category. All your tags, categories, link categories, navigation menus, and custom taxonomies are terms and they are all stored in this table.
Next is wp_term_taxonomy table which is used to describe which taxonomy a term belongs to. For example term id 1 (Uncategorized) belongs to taxonomy category, and term id 2 (Blogroll) is a link category.
In wp_term_relationship a post, links or page is related to a term from term_taxonomy table. This way a post can have multiple terms in different taxonomies.
What is Database Optimization
After setting up a database and using it for a while your database starts creating overheads which are nothing but overhead memory occupied by mysql in different databases. Too much of this overhead can affect the performance of your database. Fixing this is extremely easy as MySQL itself has provided an optimize query that you can run on your tables to optimize them:
OPTIMIZE TABLE 'wp_posts'
If you do not want to run this query manually you can optimize your tables from within phpmyadmin like this:
Or use a WordPress plugin like WP Optimize or WP DBManager. While using these plugins, you will notice that they also offer to remove your post revisions and spam comments. The reason is that many WordPress experts believe that the number of rows affected in a query seriously affect the performance of database.
So if you have like 1000 blog posts and each post has 5 revisions, then your database is dealing with 5000 blog posts, which means your queries will run a bit slower.
Another problematic thing is the data left over by the plugins you don’t use anymore. Some of these plugins may have created their own tables, you can safely delete those tables. Some plugins may have stored information in your wp_options table if you can find those then remove them as well.
WordPress Database is simpler and easier to understand and work with. Keeping it nice clean and secure is an important way to increase the performance of your website, so don’t neglect to optimize, clean up and backup your database on a regular basis.