Finding Large Autoloaded Options

Published on: April 25, 2025 | Last Update: April 25, 2025

Introduction

This query identifies large autoloaded options in your WordPress database, while intentionally ignoring short-lived transient entries.

Autoloaded options are loaded into memory on every page load, whether needed or not.
Large autoloaded entries can slow down site performance, increase memory usage, and cause longer page response times.

What This Query Does

This query retrieves the largest automatically loaded options, excluding temporary transients to focus on long-term data that may need optimization.

It queries the wp_options table where WordPress stores configuration settings and cached values.
It sorts the results by option size, showing the top 20 largest permanent autoloaded options.

Let's Take It One Step Further

`AND option_name NOT LIKE '%_transient_%'` — Why Ignore Transients?

This filter removes temporary caching entries called transients:

Transients are designed to expire automatically and usually do not cause long-term database bloat.
Ignoring them helps focus the analysis on permanent options that can actually impact site performance if left unoptimized.

To include transients: simply remove the AND option_name NOT LIKE '%_transient_%' condition from the query.

In Simple Terms:

The query finds large autoloaded options while skipping transient cache entries.
It highlights the options most likely to affect memory usage during page loads.
It limits results to the 20 largest records for easier review.

Why It's Important

Database Cleanup: Identifying and optimizing large options improves site load times and reduces server memory usage.
Integrity Check: Helps ensure plugins and themes are not storing excessively large data in autoloaded fields.

Query Explanation (Step-by-Step)

Selecting Data from a Specific Table: It pulls from the wp_options table, specifically targeting autoloaded options.
Checking for Data Existence or Conditions: It filters for options where autoload is set to yes and skips transient-related options.
Applying Limits or Filters: Results are ordered by size descending and limited to the 20 largest entries for quick analysis.

Use Case and Benefits

Quickly identify large options causing potential performance bottlenecks.
Spot configuration or plugin settings that need to be trimmed or optimized.
Improve memory usage, page load times, and overall WordPress performance.

How to Use This Query Safely

Always proceed carefully when inspecting and modifying database records:

Backup Database: Always create a full backup before making any database changes.
Verify Results: Manually review large options to understand their purpose before deleting or modifying anything.
Cleanup Process: Use WordPress functions or safe plugins to remove or optimize large options if needed.

SELECT 
  option_name, 
  LENGTH(option_value) AS option_size 
FROM 
  wp_options 
WHERE 
  autoload = 'yes' 
  AND option_name NOT LIKE '%_transient_%' 
ORDER BY 
  option_size DESC 
LIMIT 20;