SQL

Optimize Database Performance – Cleaning WP Options Table

We all know WordPress is an application written in PHP and paired with MySQL or MariaDB database. The PHP language reads or writes to the database. This happens on every WordPress request, for example, like the home page that makes up links, content, theme, or plugin, there is always a call in the database.

One database table, in particular, that is overly used on this request is the WP Options table. The WP Options table stores the site data, when this table gets large this will contribute to sluggish response time. Cleaning the WP Options table can improve page generation speed.

Image from https://codex.wordpress.org/Database_Description

This is the only table on the database that is alone or without any table relationship, assuming it would add load if this is linked to another table, a flat table by design. Here is the table definition:

CREATE TABLE `wp_options` (
  `option_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `option_name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `option_value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `autoload` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'yes',
  PRIMARY KEY (`option_id`),
  UNIQUE KEY `option_name` (`option_name`),
  KEY `autoload` (`autoload`)
) ENGINE=InnoDB;
FieldTypeNullKeyDefaultExtra
option_idbigint(20) unsignedPrimaryauto_increment
option_namevarchar(191)Unique
option_valuelongtext
autoloadvarchar(20)Indexyes

By design, three out of one are indexed properly for an optimized SELECT query.

Removing Transients

Transients are temporary data of plugins and themes stored on the WP Options table. Usually, this has expiration dates but if the plugin has been deactivated or uninstalled the value on the table would remain in the database forever. Transients can be easily identified since a “transient” word can be found in the field option_name. To remove execute a DELETE SQL statement.

DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');

Removing Sessions

Sessions are temporary data that is generated from every user visit. This will be automatically be deleted after thirty minutes of user inactivity but that is not the case all the time. Manual removal is needed to clean up the session’s data.

DELETE FROM wp_options WHERE option_name like '_wp_session_%';