[Skip to content](#main)

[→](/)

[![Avunu](/brand/avunu-logo.svg)![Avunu](/brand/avunu-logo-light.svg)](/)[Services](/services/)[Consulting](/services/consulting/)[ERP](/services/erp/)[Odoo](/services/erp/odoo/)[ERPNext](/services/erp/erpnext/)[Custom software](/services/custom-software/)[Devices & IoT](/services/devices-iot/)[Websites](/services/websites/)[Infrastructure](/services/infrastructure/)[Partners](/partners/)

[Work](/work/)[Open source](/open-source/)[Knowledge base](/kb/)[About](/about/)

[About Avunu](/about/)[Team](/about/team/)[Insights](/insights/)[Contact](/contact/)

[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

- [Services](/services/)[Consulting](/services/consulting/)
- [ERP](/services/erp/)
- [Odoo](/services/erp/odoo/)
- [ERPNext](/services/erp/erpnext/)
- [Custom software](/services/custom-software/)
- [Devices & IoT](/services/devices-iot/)
- [Websites](/services/websites/)
- [Infrastructure](/services/infrastructure/)
- [Partners](/partners/)

[Work](/work/)[Open source](/open-source/)[Knowledge base](/kb/)[About](/about/)

- [About Avunu](/about/)
- [Team](/about/team/)
- [Insights](/insights/)
- [Contact](/contact/)

[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

[Knowledge base](/kb/)[WordPress](/kb/wordpress/)WordPress database optimization queries

Search the knowledge basePress/

ESC

Jump to

[Browse the knowledge base](/kb/)[Frappe](/kb/frappe/)[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[WordPress](/kb/wordpress/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Databases](/kb/databases/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

↵select↑↓navigateescclose

Search the knowledge base

[Skip to the article](#kb-article)[WordPress](/kb/wordpress/)10 articles

- [Overview](/kb/wordpress/)
- [Admin-only custom post types with custom list columns](/kb/wordpress/custom-post-type-admin/)
- [Docket Cache baseline configuration](/kb/wordpress/docket-cache-configuration/)
- [Gravity Forms](/kb/wordpress/gravity-forms/)
- [Populate Gravity Forms image choices from an ACF image field](/kb/wordpress/gravity-forms-image-choices-acf/)
- [Register custom block styles in a classic theme](/kb/wordpress/block-styles-classic-themes/)
- [Run WordPress cron from the system scheduler](/kb/wordpress/wordpress-cron/)
- [Split a child theme's functions.php into included files](/kb/wordpress/child-theme-file-includes/)
- [WordPress database optimization queries](/kb/wordpress/database-optimization-queries/)
- [WordPress debugging snippets](/kb/wordpress/debugging-snippets/)
- [WP-CLI cookbook](/kb/wordpress/wp-cli-cookbook/)
- [wp-config.php reference](/kb/wordpress/wp-config-reference/)

Other sections[Frappe](/kb/frappe/)[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Databases](/kb/databases/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

In WordPress: 10 articles

[Overview](/kb/wordpress/)[Admin-only custom post types with custom list columns](/kb/wordpress/custom-post-type-admin/)[Docket Cache baseline configuration](/kb/wordpress/docket-cache-configuration/)[Gravity Forms](/kb/wordpress/gravity-forms/)[Populate Gravity Forms image choices from an ACF image field](/kb/wordpress/gravity-forms-image-choices-acf/)[Register custom block styles in a classic theme](/kb/wordpress/block-styles-classic-themes/)[Run WordPress cron from the system scheduler](/kb/wordpress/wordpress-cron/)[Split a child theme's functions.php into included files](/kb/wordpress/child-theme-file-includes/)[WordPress database optimization queries](/kb/wordpress/database-optimization-queries/)[WordPress debugging snippets](/kb/wordpress/debugging-snippets/)[WP-CLI cookbook](/kb/wordpress/wp-cli-cookbook/)[wp-config.php reference](/kb/wordpress/wp-config-reference/)

Other sections[Frappe](/kb/frappe/)[ERPNext](/kb/erpnext/)[Odoo](/kb/odoo/)[NixOS](/kb/nixos/)[Email](/kb/email/)[Linux](/kb/linux/)[Databases](/kb/databases/)[Containers](/kb/containers/)[Proxmox](/kb/proxmox/)[Git & Dev Tools](/kb/git-and-dev-tools/)

WordPressWordPress database optimization queries

SQL and WP-CLI commands to measure autoloaded options and post meta bloat, and to clean up orphaned meta and unwanted post types.

UpdatedOct 1, 2026

Applies toWordPress 6.6+WP-CLI 2.xMariaDB 10.11

Tagswordpressmariadbmysqlperformancewp-cli

Reading time5 min

On this page[Autoloaded Options](#autoloaded-options)[Autoload Values Changed in WordPress 6.6](#autoload-values-changed-in-wordpress-66)[Total Autoload Size](#total-autoload-size)[Largest Autoloaded Options](#largest-autoloaded-options)[Stop a Large Option From Autoloading](#stop-a-large-option-from-autoloading)[Post Meta Size by Prefix](#post-meta-size-by-prefix)[Clean Up](#clean-up)[Orphaned Post Meta](#orphaned-post-meta)[Expired Transients](#expired-transients)[All Posts of a Post Type](#all-posts-of-a-post-type)

A slow WordPress admin is often caused by a bloated `wp_options` table or millions of leftover `wp_postmeta` rows. Use these queries to find out where the weight is before you delete anything, then clean up the parts that are safe to remove.

The queries use the default `wp_` table prefix. Check yours with `wp db prefix` and adjust. You can run any query from the WordPress directory with `wp db query "<SQL>"`, or open a prompt with `wp db cli`.

## Autoloaded Options

Autoloaded options are read on every single request. Aim to keep the total under about 800 KB, which is the threshold at which Site Health starts warning.

### Autoload Values Changed in WordPress 6.6

WordPress 6.6 replaced the old `yes`/`no` values in the `autoload` column with:

| Value        | Meaning                                                             | Autoloaded? |
| ------------ | ------------------------------------------------------------------- | ----------- |
| `on` / `yes` | Explicitly autoloaded (`yes` is the legacy value).                  | Yes         |
| `off` / `no` | Explicitly not autoloaded.                                          | No          |
| `auto`       | No preference given; WordPress currently autoloads it.              | Yes         |
| `auto-on`    | WordPress decided to autoload it.                                   | Yes         |
| `auto-off`   | WordPress decided not to, usually because the value is over 150 KB. | No          |

Queries that only check `autoload = 'yes'` undercount on any site updated past 6.6. Match all the autoloaded values instead.

### Total Autoload Size

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ROUND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">SUM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LENGTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(option_value)) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">/</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1048576</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">2</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> autoload_mb</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_options</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> autoload </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IN</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'yes'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'on'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'auto-on'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'auto'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">);</span>
```

### Largest Autoloaded Options

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> option_name, autoload, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ROUND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LENGTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(option_value) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">/</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1024</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> size_kb</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_options</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> autoload </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IN</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> (</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'yes'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'on'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'auto-on'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'auto'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">ORDER BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LENGTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(option_value) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">DESC</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LIMIT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">25</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

Note

In MySQL and MariaDB, a double-quoted name like `ORDER BY "Size"` is a string literal, not a column alias, so it sorts by a constant and does nothing. Older versions of these queries had that bug. Order by the expression, an unquoted alias, or a backtick-quoted alias.

The same checks from WP-CLI:

```
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">wp</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">option</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">list</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--autoload=on</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--format=total_bytes</span>
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">wp</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">option</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">list</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--autoload=on</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--fields=option_name,size_bytes</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--format=csv</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">|</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">sort</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">-t,</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">-k2</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">-n</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">|</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">tail</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">-25</span>
```

Warning

In WP-CLI 2.x, `--autoload=on` only matches rows set to `on` or `yes`, and `wp option list` leaves out transients by default. It misses `auto`, `auto-on`, and autoloaded transients, so the SQL above gives the truer total.

### Stop a Large Option From Autoloading

When a plugin leaves a large option set to autoload (old caches, logs, leftover data from removed plugins), turn autoload off rather than deleting it:

```
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">wp</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">option</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">get-autoload</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">OPTION_NAM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">E</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">></span>
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">wp</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">option</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">set-autoload</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><</span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">OPTION_NAM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">E</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">></span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">off</span>
```

If the option belongs to a plugin you've removed, delete it with `wp option delete <OPTION_NAME>` after taking a backup.

## Post Meta Size by Prefix

This groups public meta keys (those not starting with `_`) by their first segment, which usually identifies the plugin that wrote them:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> SUBSTRING_INDEX(meta_key, </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'_'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> meta_prefix,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">       </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ROUND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">SUM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LENGTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(meta_key) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">+</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LENGTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(meta_value)) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">/</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1048576</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">2</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> size_mb,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">       </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">COUNT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">*</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> row_count</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_postmeta</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> meta_key </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">NOT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LIKE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'\_%'</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">GROUP BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> meta_prefix</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">ORDER BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> size_mb </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">DESC</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LIMIT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">25</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

Private keys (starting with `_`) often belong to WooCommerce, ACF field references (`_my_field` holding the field key), or the editor itself. To see them, group on the second segment instead:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> SUBSTRING_INDEX(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">SUBSTRING</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(meta_key, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">2</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">), </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'_'</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> meta_prefix,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">       </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ROUND</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">SUM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LENGTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(meta_key) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">+</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LENGTH</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(meta_value)) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">/</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">1048576</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">2</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> size_mb,</span>
<span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">       </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">COUNT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">*</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> row_count</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_postmeta</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> meta_key </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LIKE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">'\_%'</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">GROUP BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> meta_prefix</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">ORDER BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> size_mb </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">DESC</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LIMIT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">25</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

## Clean Up

Warning

Everything in this section deletes data permanently. Take a database backup first (`wp db export`) and run it on staging before production.

### Orphaned Post Meta

Meta rows whose post no longer exists are left behind when plugins delete posts with raw SQL. Count them first:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">COUNT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">*</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">)</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_postmeta pm</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LEFT JOIN</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_posts p </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">ON</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">p</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ID</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">pm</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">post_id</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">p</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ID</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">NULL</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

Then delete them:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">DELETE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> pm</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_postmeta pm</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">LEFT JOIN</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_posts p </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">ON</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">p</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ID</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">=</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">pm</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">post_id</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">WHERE</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">p</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">.</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">ID</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">IS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">NULL</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

On WooCommerce stores using HPOS, order meta lives in `wp_wc_orders_meta`, not `wp_postmeta`, so this query doesn't touch it.

### Expired Transients

```
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">wp</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">transient</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">delete</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--expired</span>
```

### All Posts of a Post Type

Logging and history plugins often store thousands of entries as posts, for example SMTP mail logs or admin activity history. Find the heavy post types first:

```
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">SELECT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> post_type, </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">COUNT</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">(</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">*</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">) </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">AS</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> posts</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">FROM</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> wp_posts</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">GROUP BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> post_type</span>
<span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">ORDER BY</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> posts </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">DESC</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328">;</span>
```

Then delete every post of a type you no longer need. `--force` skips the trash and also removes the posts' meta:

```
<span style="--shiki-dark:#FFA657;--shiki-light:#953800">wp</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">post</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">list</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--post_type=</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E"><</span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">POST_TYPE</span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">></span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--format=ids</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FF7B72;--shiki-light:#CF222E">|</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#FFA657;--shiki-light:#953800">xargs</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">-r</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">wp</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">post</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#A5D6FF;--shiki-light:#0A3069">delete</span><span style="--shiki-dark:#E6EDF3;--shiki-light:#1F2328"> </span><span style="--shiki-dark:#79C0FF;--shiki-light:#0550AE">--force</span>
```

Piping through `xargs` avoids "argument list too long" errors on very large sets, and `-r` does nothing if the list is empty.

Tip

Prefer the plugin's own "purge logs" setting or retention limit when it has one, so the table doesn't fill up again.

See the [WP-CLI Cookbook](/kb/wordpress/wp-cli-cookbook/) for more bulk deletion commands.

Sources

- [make.wordpress.org/core/2024/06/18/options-api-disabling-autoload-for-large-options](https://make.wordpress.org/core/2024/06/18/options-api-disabling-autoload-for-large-options/)
- [developer.wordpress.org/reference/functions/wp_autoload_values_to_autoload](https://developer.wordpress.org/reference/functions/wp_autoload_values_to_autoload/)
- [developer.wordpress.org/cli/commands/option/list](https://developer.wordpress.org/cli/commands/option/list/)
- [developer.wordpress.org/cli/commands/db/query](https://developer.wordpress.org/cli/commands/db/query/)

Need help with WordPress?

Our notes can take you a long way, but sometimes you'd rather have someone who's done it before.

[Websites](/services/websites/)

[← PreviousSplit a child theme's functions.php into included files](/kb/wordpress/child-theme-file-includes/)[Next →WordPress debugging snippets](/kb/wordpress/debugging-snippets/)

CC0This article is in the public domain ([CC0 1.0](https://creativecommons.org/publicdomain/zero/1.0/)), code samples included. Use it however helps you.

On this page

- [Autoloaded Options](#autoloaded-options)
- [Autoload Values Changed in WordPress 6.6](#autoload-values-changed-in-wordpress-66)
- [Total Autoload Size](#total-autoload-size)
- [Largest Autoloaded Options](#largest-autoloaded-options)
- [Stop a Large Option From Autoloading](#stop-a-large-option-from-autoloading)
- [Post Meta Size by Prefix](#post-meta-size-by-prefix)
- [Clean Up](#clean-up)
- [Orphaned Post Meta](#orphaned-post-meta)
- [Expired Transients](#expired-transients)
- [All Posts of a Post Type](#all-posts-of-a-post-type)

[Back to top↑](#top)

Services

- [ERP implementation](/services/erp/)
- [Odoo](/services/erp/odoo/)
- [ERPNext](/services/erp/erpnext/)
- [Custom software](/services/custom-software/)
- [Devices & IoT](/services/devices-iot/)
- [Websites](/services/websites/)
- [Infrastructure](/services/infrastructure/)

Resources

- [Our work](/work/)
- [Open source](/open-source/)
- [Knowledge base](/kb/)
- [Insights](/insights/)

Company

- [About](/about/)
- [Team](/about/team/)
- [Partners](/partners/)
- [Contact](/contact/)

Clients

- [Project portal](https://erp.avunu.net/projects)
- [Invoices](https://erp.avunu.net/)
- [Support](https://erp.avunu.net/)

[![Avunu](/brand/avunu-logo-light.svg)](/)[\<av-icon data-jx-static>\<svg viewBox="0 0 24 24" width="20" height="20" fill="currentColor" stroke="none" stroke-width="1.6" stroke-linecap="round" stroke-linejoin="round" aria-hidden="true" focusable="false">\<path d="M20.400 20.500h-3.600v-5.600c0-1.300 0-3-1.800-3s-2.100 1.400-2.100 2.900v5.700H9.400V9h3.400v1.600c.5-.9 1.600-1.900 3.400-1.900 3.600 0 4.300 2.400 4.300 5.500v6.300zM5.300 7.400a2.100 2.100 0 1 1 0-4.100 2.100 2.100 0 0 1 0 4.100zm1.800 13.100H3.600V9h3.500v11.500zM22.200 0H1.800C.8 0 0 .8 0 1.700v20.600c0 .9.800 1.700 1.800 1.700h20.400c1 0 1.800-.8 1.800-1.700V1.700C24 .8 23.200 0 22.200 0z">\</path>\</svg>\</av-icon>](https://www.linkedin.com/company/avunu)[\<av-icon data-jx-static>\<svg viewBox="0 0 24 24" width="20" height="20" fill="currentColor" stroke="none" stroke-width="1.6" stroke-linecap="round" stroke-linejoin="round" aria-hidden="true" focusable="false">\<path d="M12 .3a12 12 0 0 0-3.8 23.4c.6.1.8-.3.8-.6v-2c-3.3.7-4-1.6-4-1.6-.6-1.4-1.4-1.8-1.4-1.8-1-.7.1-.7.1-.7 1.2.1 1.8 1.2 1.8 1.2 1 1.8 2.8 1.3 3.5 1 .1-.8.4-1.3.7-1.6-2.7-.3-5.5-1.3-5.5-5.9 0-1.3.5-2.4 1.2-3.2-.1-.3-.5-1.5.1-3.2 0 0 1-.3 3.3 1.2a11.500 11.500 0 0 1 6 0c2.300-1.500 3.300-1.200 3.300-1.200.6 1.700.2 2.900.1 3.200.8.8 1.200 1.900 1.200 3.200 0 4.600-2.800 5.600-5.500 5.900.4.400.8 1.100.8 2.200v3.300c0 .3.2.7.8.6A12 12 0 0 0 12 .3">\</path>\</svg>\</av-icon>](https://github.com/Avunu)[Client portal](https://erp.avunu.net/projects)[Start a project](/start/)

Avunu LLC · 948 East Philadelphia Street, York, PA 17403 · (717) 814-8540Knowledge base articles are in the public domain[Privacy](/privacy/)[Terms](/terms/)
