The WPLift Guide to the WordPress Database: Understand, Optimize and Backup
WordPress uses a mySQL database to store, retrieve and display all the data within your website. This database is the most crucial part of the CMS. WordPress has developed a very simplistic approach to how the database is structured which makes it very easy to understand and manage, while providing the flexibility to extend it for any use.
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 understanding of WordPress and how it functions, 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
[php]// ** MySQL settings – You can get this info from your web host ** //
/** The name of the database for WordPress */
/** MySQL database username */
/** MySQL database password */
/** MySQL hostname */
/** Database Charset to use in creating database tables. */
/** The Database Collate type. Don’t change this if in doubt. */
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 the 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 WordPress powered website, it is stored in this table. This table has several fields and here is a snapshot showing some of those fields:
Contained in the WP_Posts table are the content of each post you make – author, date, post content, title, post type and so on.
One 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.
This is also usually the place that plugins and themes store their options so can become quite cluttered with junk if you dont clean it out regularly. See below on how to clean the database.
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:
[sql]OPTIMIZE TABLE ‘wp_posts'[/sql]
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 1000 blog posts and each post has 5 revisions, then your database is dealing with 5000 blog posts, which means your queries will run 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.
Reset Your Database
If you want to try out different themes and plugins or carry out any database work but then want to start afresh, there is a plugin you can install which will reset your database back to a fresh install. WordPress Database Reset allows you to clear out any junk left behind in your WP_options table or start again with a fresh database.
Backup Your Database
You can use phpMyAdmin to create a backup of your database which is always good to have in case anything goes wrong when making changes. To do this, load your database, click “Check All” and then click the “Export” button.
One the next page, make sure “SQL” is selected and click “Go”, you will then be promoted to save the file to your computer.
The WordPress Database is quite easy to understand once you start to take a good look at it – I recommend becoming familiar with it so you can understand what is happening with your site, what changes are made by themes and plugins.
Spending a little time cleaning out any junk and optimzing it can make your website more secure and also run faster with less overhead.