How Databases Work with MySQL and WordPress

In this MySQL and WordPress tutorial, we learn about How Databases Work with MySQL and WordPress. WordPress uses MySQL, an open source database management system, to store and retrieve all of your website’s information, from the content of your posts and pages to your comments, usernames and passwords.

If you need to visualize it, think of your site’s database as a filing cabinet and MySQL as the company that made it.

MySQL is a popular choice of database for web applications – Joomla! and Drupal also use it, and according to Wikipedia lots of high-profile companies like Google, Facebook, Twitter, Flickr and YouTube use it, too.

So how exactly does MySQL work with WordPress? In this article, I’ll walk you through everything there is to know about MySQL and how it interacts with WordPress, including the database architecture, storage engines, optimizing techniques and best practices for optimization and database management.

What is MySQL?

MySQL is a central component in the LAMP stack of open source web application software that’s used to create websites. LAMP stands for Linux, Apache, MySQL and PHP. MySQL is also used in the LEMP stack, which replaces Apache for Nginx (pronounced Engine-X).

WordPress uses PHP to store and retrieve data from MySQL databases, using SQL queries within the PHP markup. For example, if you’re a member of a WordPress-powered membership site, SQL is used for logging you in, retrieve your unique membership ID, check that you have an active membership, and ensure the correct profile data is displayed on the front-end.

PHP and SQL work hand-in-hand with WordPress, allowing you to create dynamic content based on many different factors, such as your IDs and user roles. This allows you to do things like hide or show content to specific users, such as admins, editors and subscribers. Without SQL, and MySQL none of this would be possible.

Plugins and themes also use your database to store data, such as options, and then use SQL within PHP markup to query the database and output content dynamically.

It’s worth mentioning that if you run a small website (i.e. a blog about your cat), you really don’t need to mess with SQL. It’s only when you work on enterprise-level websites that having SQL knowledge becomes essential.

WordPress Database Architecture or: Tables, Tables, Tables

To help you understand how exactly WordPress works with MySQL, let’s run through the tables WordPress stores in a typical database.

WordPress has a fairly straightforward and uncomplicated database schema. It consists of 11 tables, which are used by core components of WordPress and cannot by deleted or removed.

wp_commentmeta – Stores the metadata from all comments left on your WordPress posts and pages, including custom post types.

wp_comments – Stores all comments made on your site, including published, draft, pending and spam comments.

wp_links – Holds all information entered into the links manager feature of WordPress, this is rarely used nowadays, with the link feature itself becoming deprecated from WordPress 3.5 and hidden by default on new installs.

wp_options – Not only are all WordPress options stored in this table, such as your reading and discussion settings, but it’s more common now for plugins to use wp_options for storing saved settings as opposed to a custom table.

wp_postsmeta – As you’ve most probably guessed this table stores all metadata associated with your posts and pages.

wp_posts – Stores all your posts, as well as your pages and also your navigation/menu items.

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

wp_term_relationships – Posts are associated with categories and tags from the wp_terms table, and this association is maintained in the wp_term_relationships table. The association of links to their respective categories is also kept in this table.

wp_term_taxonomy – Describes the taxonomy such as a category, link, or tag for the entries in the wp_terms_table.

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

wp_users – All your users are stored within this table. Remember, data such as passwords are serialized.

Multisite Databases Are Structured Very Differently

The database for a Multisite install is structured very differently to that of a standalone site, so if you manage one or the other or both it’s important that you understand the differences so you can manage your websites effectively.

wp_blogs – Each site created on a Multisite network is stored in this table.

wp_blog_versions – Stores the current database version of each site in the network and is primarily used in the update process of your network. It’s updated as each site is upgraded.

wp_registration_log – Logs the admin user creates when each new site is registered.

wp_site – This table contains the main site address.

wp_sitemeta – Each site has site data; this table stores the site data including various options including the site admin.

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

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

Site-specific tables are also added to your database, i.e. wp_2_commentmeta, wp_2_comments, wp_2_links. Your main site data is stored in existing unnumbered tables, and subsequent sites have their data stored in numbered tables following the naming structure of the main site tables.

Plugins Use Your Database, Too

When you install a plugin it will use your database to store and retrieve data related to that plugin. For example, a custom fields plugin would save the fields it creates to the database and then retrieve them later to display on associated posts. Without the database, the plugin wouldn’t be able to store any fields it creates, associate a field with a post or query values for display on the front-end.

Plugins can either use the default WordPress database tables, such as wp_posts or wp_postsmeta, or create custom tables. One popular example of a plugin creating its own tables is WooCommerce, which creates eight custom tables to store and retrieve product IDs, order items, tax rates and other product information.

If you’re worried about plugin creating tables in your database, don’t – it’s common for plugins to do this. While it’s preferable to use existing tables, such as wp_options, for storing plugin data, it isn’t always possible, especially with more complex plugins like WooCommerce.

Note: It’s a good idea to delete custom tables from your database when you remove a plugin from your site, otherwise over the lifetime of your install you’ll amass a collection of unused tables in your database. Some plugins do come with the option to automatically delete all data associated with a plugin when you uninstall it. Keep in mind that you should only delete custom tables when you’re absolutely sure you’re not going to use a particular plugin again because there’s no going back.

MySQL Storage Engines Explained

MySQL uses storage engines to store, handle and retrieve information from a table. While MySQL provides support for 13 different storage engines, the two most commonly used options are MyISAM and InnoDB.

Most of the time, the default storage engine as defined in your MySQL configuration file is typically MyISAM, and this is what people usually go with. Since many people don’t bother taking the time to choose a storage engine, they just use the default.

If you do decide to select a storage engine, with WordPress it’s a decision that it made somewhat easier – while MyISAM may be quicker for reading, InnoDB is quicker for both reading and writing thanks to its row locking mechanism. As WordPress relies heavily on both reading and writing, InnoDB is the best choice.

It’s worth noting that by default tables created in phpMyAdmin use the MyISAM storage engine. Typically, this means that if you use shared hosting or a non-specialist WordPress host your tables will use MyISAM rather than InnoDB. If you want to change your storage engine, you can use the following SQL query (which you can execute in your favorite database management tool, such as phpMyAdmin):

SET default_storage_engine=InnoDB;

Note: For some incredibly strange reason, tables created in/by phpMyAdmin by default use MyISAM. This means that if you use shared hosting or a nonspecialist host, your tables will be MyISAM. Fear not! You can change the engine being used by your database. To change one table you can use:

ALTER TABLE table_name ENGINE=InnoDB;

Changing the storage engine table by table can be a time-consuming process, in which case you might want to take a look at Pantheon’s excellent tutorial.

You may now be thinking, “Great! But what about plugins that create custom tables – which engine do they use?” The answer is: They can use a mix. Some declare SQL statements to use InnoDB, while others use MyISAM. Overall, it’s best to keep an eye on your database after installing a new plugin that creates custom tables and check which MySQL engine it’s using.

WP_Query

The WP_Query class is an extremely powerful WordPress query that you can use to access posts in your database. We’ve already covered WP_Query extensively on this blog before, so I’m really only just pointing it out here.

For a more comprehensive guide to WP_Query, check out our post An In-Depth Guide to Conquering WP_Query.

Optimizing Your WordPress Database

One of the most common reasons for a slow site is a poorly maintained non-optimized database.

We’ve looked at the advantages of choosing a database engine and now we’ll look at how you can remove some of the junk that’s stored on your site to make it leaner.

For a comprehensive guide on how to optimize your database, check out our guide Optimizing Your WordPress Database – A Complete Guide.

Before you get started with optimizing your database, it’s a good idea to create a full backup first in case you run into any trouble. I highly recommend Snapshot Pro, our backup plugin. It can backup and restore your entire site with one click, complete with Dropbox and S3 integration.

Snapshot Pro lets you quickly and easily backup your WordPress site for restoration later in case of emergency. It’s better to be safe than sorry!

Only Install Plugins You Are Actually Going to Use

A simple way to optimize your database without actually doing anything is install plugins you will use and not install plugins for the sake of installing plugins. It’s easy to get drawn into activating shiny new plugins! Just remember that for every plugin you install, new data will be created that in turn will fill up your database.

There are plugins that known for storing significant amounts of data, and these typically fall into four categories:

Security Plugins – Most security plugins collect and store information on attacks made against your site to protect it from future attacks, spam, login attempts and much more.
Statistics Plugins – These plugins don’t pull in data from a third party source, i.e Google Analytics, and instead store metrics such as page, visits, browsers, keywords and more in your database.
Anti-Spam Plugins – Due to the very nature of anti-spam plugins they store massive amounts of data just like security plugins, including information such as IP addresses, email addresses, countries, etc.
Popular Posts Plugins – Keeping track of things like views and likes across hundreds or thousands of posts adds up and can make your database grow. Best to keep these plugins to a minimum.

So should you stop using the above plugins? Yes and no. While you should take spam and security of your site very seriously, unless needed for the type of site you run try and avoid stat and popular post type plugins.

Spam

Spam comments are one of the main causes of a bloated database if not properly maintained. I’ve seen sites with tens of thousands of spam comments. Luckily, it couldn’t be simpler to remove them.

Either run an SQL command like this:

DELETE FROM wp_comments WHERE comment_approved = ‘spam'

Or, if you log in to your WordPress dashboard and go to Comments > Spam you should see an “Empty spam” button. Click it and every spam comment on your install will disappear for good. Before you do remove any spam comments, be sure to check that they are indeed spam. It’s common for comments to be marked as spam when they are, in fact, genuine.

If you don’t want to deal with spam manually, the most popular plugin for stopping spam in its tracks is Akismet, which allows you to set spam comments to be automatically deleted.

Revisions

WordPress 2.6 introduced a post revision feature, which allows you to store previous versions of a post, i.e. saves all drafts and updates. Contrary to popular belief, only one autosave is kept per post, automatically removing the old autosaved version. This means that your table won’t keep growing with autosaves. However, your table will increase every time you click “Update” on your post or save a new draft.

While revisions are useful and I wouldn’t personally disable them, nor would I recommend disabling them, you can save space in your database by removing old revisions. To keep a maximum number of revisions, you can add a handy define to your wp-config.php file:

define( 'WP_POST_REVISIONS', 5 );

Just change the number to however many revisions you want to keep. Entering 1 or more stores the number of revisions plus the autosave, -1 stores every revision, and 0 sets it to false and stores no revisions except the autosave.

To remove revisions from existing posts, you’ll need to either run an SQL command to remove them or use a WordPress optimization plugin to remove them. If you wish to use SQL, you can run a command like this:

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

This query deletes all post revisions from those posts but also removes all meta and associated taxonomies. Remember, though, that this deletes all revisions and not just some.

If you would rather use a plugin to remove revisions, check out Optimize Database after Deleting Revisions. Not only does it allow you to remove revisions, it’s also Multisite compatibility and allows you to delete things like unused tags, orphan post meta and much more.

Deleting Unused Tables

Plugins that create custom tables quite often don’t delete them on uninstallation. If you remove a plugin and don’t plan on using it again, you’ll want to remove the table it creates. While there are plugins such as WPDBSpringClean that can do this for you, it hasn’t been updated in over two years and in general you shouldn’t use a plugin for deleting tables.

There’s no easy way to know what database tables aren’t being used, though generally plugins name their tables using the name of the plugin or the main class of the plugin making them easier to find. Of course, like I’ve already mentioned, before you delete tables or modify your database make sure to create a full backup.

Manually Optimizing Your Database

MySQL comes with an OPTIMIZE query that, according to the official manual, “Re-organizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.” The exact changes made to each table depend on the storage engine used by that table.

You can run an OPTIMIZE query using a database management tool, such as phpMyAdmin.

Optimizing Your Database with a Plugin

If you would rather a plugin do all the work for you, WP-Optimize is a popular free option that’s active on 500,000+ WordPress installs. It can remove post revisions, old metadata, draft posts, and also bulk delete trashed comments.

It can also apply the native OPTIMIZE query without you having to use a database management tool or a manual query in your database management tool. Too easy!

Repairing your WordPress Database

If your database becomes corrupted for whatever reason, don’t panic! You can edit your wp-config.php file to repair it:

define('WP_ALLOW_REPAIR', true);

When you’ve saved your file, fire up your browser and go to www.example.com/wp-admin/maint/repair.php

On the repair screen, you can either just repair your database or repair and optimize your database. Once you’ve picked either option, WordPress will then try and automatically repair your database.

Sometimes repairing your database this way doesn’t work, or only works partially. In this instance, open up phpMyAdmin and try repairing your database table-by-table.

But what if repairing your database that way also doesn’t work? Unless you’re an SQL ninja and data recovery expert, this is the point where you need to resort to restoring a previous backup of your site if you have one.

How WordPress Database Caching Works

I could go on forever about caching and WordPress as there’s a lot to know, but for this article I’ll cover the most important things you need to know.

Transients API

The Transients API is very similar to the Options API in WordPress (a simple and standardized way of storing data in the database that makes it easy to create, access, update, and delete options), but with the added feature of an expiration time, which simplifies the process of using the wp_options database table to temporarily store cached information.

In WordPress, you can use transients for constantly changing data that you want to expire and update, but also as replacements for more intensive database queries that you want to cache.

One downside is poorly coded transients; maybe the transient has an expiration time but wasn’t set to be deleted, resulting in a transient trying to be loaded, which doesn’t exist. Also, site owners installing transient deletion plugins has gained in popularity; deleting transients used by plugins and themes that shouldn’t be deleted can cause multiple issues for your site.

Ultimately, you should only delete transients if you know exactly what you’re doing and what they are for – don’t just bulk delete all transients as there’s a good chance you’ll end up with a broken site.

Memcached

Using Memcached on your site allows you to speed up intensive database queries (data and objects) in RAM to reduce reads on your database. This allows your pages to be loaded more quickly as the data is already there without having to make a query.

One downside, like with all caching, is that if you update your post/page/site and it’s already cached, you’ll need to flush the cache before the changes are displayed.

One mistake many people often make with Memcaching is installing a plugin such as W3 Total Cache, seeing the setting for Memcache, and activating it without actually having Memcached setup. You can’t just set the option without configuring Memcached database/server side first! An incorrectly configured Memcached (or any object caching, for that matter) can wreck havoc on your site and database, causing among other things transient issues causing issues with automatic updates and plugins/themes that rely on transients.

Redis

Without a doubt, my favorite method of database driven caching with WordPress is Redis, which makes a massive difference in page load times. Unlike Memcached, Redis has built-in persistence; like Memcached, Redis also is an in-memory data structure store (storing your data in RAM).

You can use the Redis Object Cache plugin for connecting Redis to your WordPress site. Remember, though, that first you’ll need to set up Redis and configure your caching. One way you can do this is with the Predis script or HHVM’s Redis extension (only if using HHVM in place of PHP).

Make sure you configure Redis sensibly – don’t store large blocks of data on each key and keep to a sensible number of keys as there’s no point in using database caching if you’re going to make thousands of Redis calls, resulting in longer object cache transactions.

Whether you use Memcached or Redis, there is an important difference between the two: Memcached is a memory store caching system, whereas Redis is a proper data structure server, allowing it to be used as a real data store rather than just a volatile cache. Check out this great answer over on StackOverflow about why you should use Redis over Memcached if you don’t already have a large investment setup with a Memcached system.

MariaDB

MariaDB is a fork of MySQL by one of the original founders and developers of MySQL after it was acquired by Oracle.

MariaDB is known for being significantly faster, thanks to the quicker replication and pool of threads allowing tens of thousands of connections with no noticeable I/O slowdown. MariaDB also offers a greater number of storage engines with drop in replacements for more popular storage engines like InnoDB.

While Memcached isn’t available for use with MariaDB, you can use the excellent Query Cache for setting up database caching with Maria DB.

So should you switch to MariaDB? It’s open source, quicker and, overall, offers some great features. If you have a medium-large site, yes, I would definitely recommend it. But if you are on cheap shared hosting with a small site, it’s not worth the time or effort.

Ultimately, MariaDB is my preference over MySQL, especially due to its handling of connections, which means less of those dreaded “Can’t establish a connection to database” message. Which isn’t to say that MySQL can’t be dramatically improved itself through optimization and caching which I’ll explore further below.

WordPress and the wpdb Class

The wpdb class in WordPress is at the core of all database interactions between the core software and your database. It’s also used by both plugins and themes.

It’s important to always remember to escape your SQL commands to prevent against SQL injection attacks. There have been multiple cases over the past few years where well-known plugins have contained vulnerable SQL code, which hackers have exploited.

I won’t go too in-depth on this topic. Instead, for further reading check out the WordPress Codex entry on the wpdb class, escaping SQL in WordPress and creating custom tables in plugins for a great start to WordPress and the wpdb class.

Tools to Help You Manage Your Database

Most web hosts offer some form of access to your database, usually phpMyAdmin, which provides an easy to use graphic user interface for working with your database.

phpMyAdmin

phpMyAdmin is a tool written in PHP intended to handle the administration of MySQL over the web.

A free and open source script for database management. phpMyAdmin offers a simple way to optimize, repair, import, export and run SQL operations on your database. It works with both MySQL and MariaDB.

Navicat

Navicat is a premium database management and design tool.

Navicat is a premium fully-featured database management tool. Along with all the standard features of any good database management tool, such as import/export, table viewer, optimization, and repair, it also offers an SQL builder/editor and an object designer. Like phpMyAdmin, it works with both MySQL and MariaDB.

Understanding How MySQL Works with WordPress

Databases are an integral part of WordPress, providing the backbone (or filing cabinet) of your sites. Ensuring your sites run smoothly, are optimized and regularly backed up can be a time-consuming task, but with the right knowledge, tools and plugins, managing your database is fairly straightforward and simple to do.

Source: https://wpmudev.com

#wordpress #mysql #database

What is GEEK

Buddha Community

How Databases Work with MySQL and WordPress

Joe Hoppe

1595905879

Best MySQL DigitalOcean Performance – ScaleGrid vs. DigitalOcean Managed Databases

HTML to Markdown

MySQL is the all-time number one open source database in the world, and a staple in RDBMS space. DigitalOcean is quickly building its reputation as the developers cloud by providing an affordable, flexible and easy to use cloud platform for developers to work with. MySQL on DigitalOcean is a natural fit, but what’s the best way to deploy your cloud database? In this post, we are going to compare the top two providers, DigitalOcean Managed Databases for MySQL vs. ScaleGrid MySQL hosting on DigitalOcean.

At a glance – TLDR
ScaleGrid Blog - At a glance overview - 1st pointCompare Throughput
ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads. Read now

ScaleGrid Blog - At a glance overview - 2nd pointCompare Latency
On average, ScaleGrid achieves almost 30% lower latency over DigitalOcean for the same deployment configurations. Read now

ScaleGrid Blog - At a glance overview - 3rd pointCompare Pricing
ScaleGrid provides 30% more storage on average vs. DigitalOcean for MySQL at the same affordable price. Read now

MySQL DigitalOcean Performance Benchmark
In this benchmark, we compare equivalent plan sizes between ScaleGrid MySQL on DigitalOcean and DigitalOcean Managed Databases for MySQL. We are going to use a common, popular plan size using the below configurations for this performance benchmark:

Comparison Overview
ScaleGridDigitalOceanInstance TypeMedium: 4 vCPUsMedium: 4 vCPUsMySQL Version8.0.208.0.20RAM8GB8GBSSD140GB115GBDeployment TypeStandaloneStandaloneRegionSF03SF03SupportIncludedBusiness-level support included with account sizes over $500/monthMonthly Price$120$120

As you can see above, ScaleGrid and DigitalOcean offer the same plan configurations across this plan size, apart from SSD where ScaleGrid provides over 20% more storage for the same price.

To ensure the most accurate results in our performance tests, we run the benchmark four times for each comparison to find the average performance across throughput and latency over read-intensive workloads, balanced workloads, and write-intensive workloads.

Throughput
In this benchmark, we measure MySQL throughput in terms of queries per second (QPS) to measure our query efficiency. To quickly summarize the results, we display read-intensive, write-intensive and balanced workload averages below for 150 threads for ScaleGrid vs. DigitalOcean MySQL:

ScaleGrid MySQL vs DigitalOcean Managed Databases - Throughput Performance Graph

For the common 150 thread comparison, ScaleGrid averages almost 40% higher throughput over DigitalOcean for MySQL, with up to 46% higher throughput in write-intensive workloads.

#cloud #database #developer #digital ocean #mysql #performance #scalegrid #95th percentile latency #balanced workloads #developers cloud #digitalocean droplet #digitalocean managed databases #digitalocean performance #digitalocean pricing #higher throughput #latency benchmark #lower latency #mysql benchmark setup #mysql client threads #mysql configuration #mysql digitalocean #mysql latency #mysql on digitalocean #mysql throughput #performance benchmark #queries per second #read-intensive #scalegrid mysql #scalegrid vs. digitalocean #throughput benchmark #write-intensive

Juanita Apio

1678652940