MySQL and WordPress: How does Database work?

WordPress uses the MySQL open source database management system to store and export all website information, from the content of posts, to comments, usernames and passwords.

If you need to visualize this information, imagine the database is a full file cabinet, and MySQL is a service company that arranges these documents.

MySQL is the popular database choice for web applications – Joomla! and Drupal uses MySQL, and according to Wikipedia, many "big face" companies like Google, Facebook, Twitter, Flickr and Youtube also use this system.

So how exactly does MySQL and WordPress work together? In this article, I will explain everything you need to know about MySQL and how to interact with WordPress, including database structure, storage engines, optimization techniques and database management.

What is MySQL?

MySQL is a key component in the LAMP stack of open source web application software used to build websites. LAMP stands for Linux, Apache, MySQL and PHP. MySQL is also used in LEMP stack (replacing Apache for Nginx).

WordPress uses PHP, with SQL queries in PHP markup to store and export data from MySQL databases. For example, if you are a member of a website that uses WordPress's management system, SQL is used to log in, get membership IDs, check if you have active membership, and ensure the front-end is displayed. correct data profile.

PHP and SQL work in parallel in WordPress, helping you create dynamic content based on many different elements, like your IDs and user roles. This allows you to do many things like hide or show content to specific users, like admins, editors or subscribers. These tasks cannot be completed if SQL and MySQL.

Plugins and themes also store data in your database, like options, and use SQL in PHP markup to query the database and output content.

In particular, if you are managing a small website (like a cat blog), you really don't need to "play" with SQL.

WordPress database structure: Tables, Tables, Tables

To help you understand the exact working mechanism between WordPress and MySQL, consider the WordPress tables stored in a typical database.

WordPress has a clear and simple database schema, including 11 tables, used by WordPress core components and cannot be deleted.

wp_commentmeta – Store metadata from all comments on WordPress posts and pages, including custom post types.

wp_comments – Store all comments made on the site, including published, draft, pending and comments.

wp_links – Keeping all the information around WordPress's manager features, this feature is currently rarely used, with the link feature being increasingly separated from WordPress (3.5) and by default hidden on new installs.

wp_options – this table not only stores WordPress options, but the current plugin also uses this table to store settings instead of custom tables.

wp_postsmeta – This table stores all metadata associated with posts and pages.

wp_posts – Store all posts, as well as pages and navigation / menu items.

wp_terms – This table stores categories for posts, links, and tags.

The wp_term_relationships – Posts are linked to categories and tags from the wp_terms table, and this link is maintained in the wp_term_relationships table. Links of links to corresponding categories are also kept in this table.

wp_term_taxonomy – Describes category classification, link, or tag entries for wp_terms_table .

wp_usermeta – Stores the metadata of all users from wp_users table.

wp_users – All users are stored in this table. Remember, data like passwords are serialized.

Multisite Databases has a very different structure

The database for Multisite install is structured very differently from the single site database. To manage more effectively for each database, you need to know the following differences:

wp_blogs – Each site created with Multisite network will be stored in this table.

wp_blog_versions – Stores the current database version of each site on the network and is mainly used during network upgrades. The table will update when each site is updated.

wp_registration_log – Record admin users creates when each new site is registered.

wp_site – This table contains the main site address.

wp_sitemeta – Each site has a site data; This table contains site data including many customizations including admin site.

wp_users – Contains all users, and this field is also used in single site install; includes two extra fields / rows spam and deleted.

wp_usermeta – When using Multisite, this table stores the metadata of users for each site (this is not a re-creation of wp_usermeta in a single site install).

Tables for each site are also added to the database, such as wp_2_commentmeta, wp_2_comments, wp_2_links. The data of the main site is stored in the numbered tables already available, and the following sites have data stored in the numbered tables following the structure names of the man site tables.

Plugins also use databases

When you install a plugin, the plugin will use your database to store and retrieve data related to that plugin. For example, a custom field plugin will save the field created in the database and then export it to display on the linked posts. If there is no database, the plugin cannot store any field it creates, or link a field with post and querry values ​​to display on the front-end.

Plugins can use WordPress's default database tables (like wp_posts or wp_postsmeta ) or create custom tables. WooCommerce is a typical example of creating custom custom tables, up to eight custom tables to store and export product IDs, order items, tax rates and other product information.

Don't worry too much when the plugin creates tables in your database, this is a very normal thing in the plugin world. While the plugin will prioritize the use of available tables (like wp_options ) to store plugin data, there will be exceptions, especially with complicated plugins like WooCommerce.

Note: If you delete the plugin from the site, delete the custom tables from the database. Some plugins have built-in options to automatically remove all data linking to the plugin when you unistall. Remember, you should only delete the customs tables of that plugin when you are sure you will not be able to use that plugin again, because it will be impossible to recover it.

High-quality scalability & traceability Database design has never been so easy with an expert from Tiki on August 22 in Hanoi. Register here !

Explain more about MySQL storage engines

MySQL uses storage engines to store, process, and export information from a table. Although MySQL provides up to 13 different storage engines, only the two MyISAM and InnoDB options are the most popular.

Mostly, the default storage engine in MySQL configuration file is usually MyISAM, and also the most chosen storage engine. (because many people don't have the time to be picky, just leave it to default)

If you decide to choose another storage engine, with WordPress, this is an easy option – MyISAM can be faster when reading, while InnoDB is faster when both reading and writing thanks to the row locking mechanism. Because WordPress depends a lot on both reading and writing, InnoDB is the best choice.

Also, the default tables created in phpMyAdmin use the MyISAM storage engine. Usually, this means that if you use shared hosting or non-specialist WordPress host, tables will use MyISAM and not InnoDB. If you want to change the storage engine, you can use the following SQL query (you can execute this query in the custom database management tool, for example, phpMyAdmin):

SET default_storage_engine=InnoDB;

Note: For some strange reason, tables created in / by phpMyAndmin will use MyISAM by default. In other words, if you use shared hosting or nonspecialist host, tables will be MyISAM. Do not worried! You can change the engine that the database uses. To change a table, you can use:

ALTER TABLE table_name ENGINE=InnoDB;

Changing each table is very time consuming, if you want faster, you can read Pantheon's excellent tutorial .

Now you may be thinking, "Great! What about custom tables plugin – what engine do they use? ”The answer is: They can use a lot of plugins. Some declare SQL statements to use InnoDB, while others use MyISAM. In general, you should pay attention to the database after installing a new plugin (have custom tables created) to see which MySQL engine is running.

WP_Query

Class WP_Query is an extremely powerful WordPress query, you can use this query to access post in the database.

Optimize WordPress Database

Lack of database optimization and poor maintenance are common reasons for slow sites.

We have explored the benefits of selecting a suitable database engine and now we will look at how you can remove junk on the page, to make the site lighter.

Before optimizing the database, you should create a backup first to take precautions. The Snapshot Pro backup plugin is a pretty good option. This plugin can backup and restore the whole site with just one click.

Only install the Plugins that you will actually use

A simple way to optimize the database (which really doesn't need to be done at all) is to install only the plugins that you will only use. Anyone is very vulnerable to "tempting" the new sparkling plugins! Just remember, the more plugins you install, the more new data will grow in the database.

There are a number of plugins known for their ability to store large amounts of data, and these plugins are divided into 4 groups:

• Security Plugins – Most security plugins collect and store information about attacks to the site, to protect the site somewhat from future attacks, spam, login attempts, etc.
• Statistics Plugins – These plugins do not collect data from a third source (like Google Analytics) but store parameters such as page, visits, browsers, keywords … in the database.
• Anti-Spam Plugins – Due to its nature, anti-spam plugins store large amounts of data much like security plugins, including information such as IP addresses, email addresses, countries, etc.
• Popular Posts Plugins – Keep track of information such as views and likes of hundreds of thousands of posts.

Should you stop using the plugins? Yes and no. Although you have to pay attention to spam and security issues on the site, but if these "famous" plugin types are not suitable for your site, it should be avoided and removed if possible.

Spam

Spam comments is one of the main reasons that database "stout" is not properly maintained. But luckily, eliminating the comments is extremely simple.

You can run the following SQL command:

DELETE FROM wp_comments WHERE comment_approved = 'spam'

Or, if you login to WordPress dashboard and on Comments> Spam, you will see the "Empty spam" button. Click on it and all spam on the page will disappear. Before removing spam comments, make sure they are indeed spam. Many comments are not spam, but are often labeled as spam.

If you don't want to handle spam manually, Akismet is the most popular plugin for this job, allowing you to set delete spam comments automatically.

Revisions

WordPress 2.6 introduces the post revision feature, allowing you to store previous versions of the article. Contrary to many people's guesses, each article only keeps an autosave, and automatically deletes the old autosave. As such, your tables will not have to carry your autosaves anymore. However, the table will expand with each time you lick "Update" on a new post or save draft.

Because of the usefulness of this feature, my friends and I probably shouldn't turn it off, but you can still save space in the database by deleting the old revisions. To maintain a maximum amount of revisions, you can add a convenient define to wp-config.php file:

define( 'WP_POST_REVISIONS', 5 );

You simply change the number in brackets to the maximum number of revisions you want to keep, -1 will store all revision, and 0 will not store revisions except autosave.

To remove revisions from existing posts, you will have to run the SQL command or use the optimal plugin in WordPress. If you choose to use SQL, you can run the following command:

This query removes all post revisions from these posts, and also deletes all related meta and taxonomies. However, remember this query will remove ALL revisions.

If you want to use the plugin, check out Optimize Database after Deleting Revisions . This plugin not only allows you to remove revisions, but also multisite-compatible plugins that allow you to delete certain items such as unused tags, orphan post meta, …

Delete the unused Tables

Plugins that create custom tables usually do not delete these tables when uninstalled. If you delete the plugin and no longer intend to use that plugin again, you will want to delete these tables. WPDBSpringClean is also a plugin suitable for this situation, but this plugin has been "abandoned" by the author for two years, and in general, you should not use the plugin to remove tables.

Determining whether the table is being used is not easy. In general, plugins name their tables by the name of the plugin or the main class of the plugin (that's easier to find). Of course, as I said above, before deleting tables or changing the database, always backup fully.

Optimize database manually

MySQL supports query OPTIMIZE, but according to the official manual, "reorganize physical storage of table data and related index data, to reduce storage space and improve I / O performance when accessing tables." Accurate changes to each table are based on the storage engine used by the table.

You can run OPTIMIZE query when using database management tool, like phpMyAdmin.

Optimizing Your Database with a Plugin

If you want a "dirty hand" plugin for you, WP-Optimize is currently a free, active option with over 500,000 installs on WordPress. This plugin can remove post revisions, old metadata, draft posts, and delete spam comments in bulk.

Also, WP-Optimize can apply OPTIMIZE query without using database management tool or manual query in database management tool.

Fix WordPress Database

If your database is corrupted due to various reasons, don't panic! you can edit the wp-config.php file to fix:

define('WP_ALLOW_REPAIR', true);

When you have saved the file, start the browser and go to www.example.com/wp-admin/maint/repair.php

On the repair screen, you can only repair the database, or both repair and optimize. Once you have chosen, WordPress will then automatically attempt to fix the database.

Sometimes, this fix does not work, or only partially works. In this case, open phpMyAdmin and fix the database for each table.

But if this is also not effective? Unless you are a SQL ninja and a data recovery expert, this is where you must restore the backup earlier if any.

How does the caching WordPress database work?

Caching in WordPress is a very broad concept. Therefore, with limited capacity in this article, I will only introduce the most important parts you need to know.

Transients API

Transients API is very similar to Options APi in WordPress (simple data storage and standardization in database; makes it easy to create, access, update, and remove options), but with expiration time feature, the process Using wp_options database table to temporarily store cached information will become simpler.

In WordPress, you can use transients to constantly change the data you want to expire and update, or to replace the more powerful database queries you want to cache.

The only drawback is that transients are very sketchy; It is possible that the transient has an expiration time, but is not set to be removed, resulting in the transient trying to load, but does not exist. Moreover, the transient deletion plugins installation on the site is increasingly popular; and deleting the transients used by themes and plugins (which we should not delete) can cause many annoying problems.

On top of that, you should only delete the transients when you know what they are for – just start erasing all the transients that will ruin the entire site.

Memcached

Using Memcached on the site allows you to accelerate the intensive database of queries (data and objects) in RAM to reduce reads on the database, which allows the page to load faster because the data is already prepared without query execution.

One drawback, as with every caching, is that if you update the post / page / site when cached, you will have to flush cached before the change is displayed.

A mistake that many people make with Mencaching is installing plugins like W3 Total Cache, looking at Memcache settings, and activating without actually installing Memcache. You cannot set only options without customizing the Memcached database / server side first! Modifying Memcached (or any other object caching program) is improperly disruptive to your site and database, causing many problems (besides transient problems) about updating updates and additional plugins / themes and transients.

Redis

Undoubtedly, my preferred caching method (WordPress) is Redis, which greatly improves the page load time.

You can use the Redis Object Cache plugin to connect Redis to WordPress site, but must set up Redis and config caching first. You can use Predis script or HH's Redis extension (only when using HHVM instead of PHP).

It is necessary to configure Redis properly – do not store each big data block on each key and stick to an appropriate number of keys because there is no need to access database caching if you intend to perform thousands of Redis calls (resulting in object cache) transactions longer.

Whether you use Memcached or Redis, both are quite distinct: Memcached is a caching system, in Redis it is a data structure server, allowing it to be used as a data warehouse. not just volatile cache.

MariaDB

MariaDB is the brother of MySQL, built by one of MySQL's many developers and founders after being acquired by Oracle.

MariaDB is known for its slightly higher speed, thanks to faster replication and thread pools that allow up to tens of thousands of connections without significantly slowing down I / O. MariaDB also offers a large number of storage engines that can be a perfect substitute for storage engines like InnoDB is currently quite popular.

Although Memcached is not available with MariaDB, you can use Query Cache to set up database caching with Maria DB.

So should you turn to MariaDB? This tool is open, faster, and generally offers many great features. If you are managing a medium-large site, I strongly support you to switch to MariaDB. But with small sites using shared hosting cheap, maybe not worth the time and effort to do so.

Overall, I rate MariaDB even higher than MySQL, especially because of the tool's connection processing capability. However, we still have ways to improve MySQL through optimization and caching.

High-quality scalability & traceability Database design has never been so easy with an expert from Tiki on August 22 in Hanoi. Register here !

WordPress and class wpdb

The wpdb class in WordPress is at the core of every database interaction between core software and database. Many plugins and themes also use this class.

To prevent SQL injection attacks, you must always remember to exit SQL commands. There have been many cases of hackers taking advantage of vulnerable SQL code in popular plugins.

Database management tool

Most web hosts provide a variety of ways to access the database. Typically, as with phpMyAdmin is the graphic user interface form.

phpMyAdmin

phpMyAdmin, an open source script for database management, provides a simple solution to optimize, fix, import, export and run SQL operations on the database; can be used with both MySQL and MariaDB.

Navicat

Navicat is a complete database management tool. Along with all the standard features of any good database management tool, such as import / export, table viewer, optimization, fix, and even SQL builder / editor with object designer. Like phpMyAdmin, Navicat works both with MySQL and MariaDB.

Understanding How MySQL Works with WordPress

Databases are an integral part of WordPress, which is the pillar of the site. The database makes sure the site runs smoothly. Maintenance and optimization can be time-consuming, but equally simple and easy to perform properly.

ITZone via wpmudev