WordPress is designed to be a flexible application framework, but that flexibility isn't always a good thing, especially for the database. As your site grows, the wp_postmeta and wp_options tables can grow to millions of lines of code.
A website may appear fast on the frontend due to page caching, it shows us that a database is having difficulty finding data.
On a huge database without indexing, MySQL would have to perform a full table scan. This means it looks at every row on disk to find a match for your one query. If you have 2,5 million rows of post meta, this process would be incredibly slow and resource-intensive.
wp_options and its 1MB buffer
wp_options grows and acquires new entries with every plugin you install and every setting you make.
The wp_options table is arguably the most common source of slowdown. WordPress is designed to query every row where autoload is set to 'yes' on every page load to populate the object cache.
Depending on the hosting platform you are using, these autoload values may be stored as a single line in the persistent object cache. This is where the 1MB buffer limit comes in as a critical guardrail. If the total size of the data being autoloaded exceeds this 1MB limit, the cache level may reject the request. This forces WordPress to repeatedly query the database for a huge set of data that is being loaded continuously, resulting in a loop of failed requests that lead to 502 Bad Gateway errors.
Even if you stay under the 1MB limit, an overly large wp_options table makes index lookups less efficient. Keeping a wp_options table with few autoload = 'yes', ideally under 700kb, is essential to maintaining a healthy object cache and ensuring a fast Time to First Byte (TTFB) for all requests.
The wp_postmeta nightmare
The wp_postmeta table is a “vertical” table that uses key/value pairs. By default, the meta_value column is not indexed because it uses a longtext data type.
If you run a meta_query that filters by meta_value, such as searching for all products within a certain value range, MySQL is forced to scan every metadata row. When your table has millions of rows, these queries can take a while to execute.
What can you do;
If you don't know the structure of a WordPress database, it's best to do nothing. If you do, take a full backup and use it. WP DB Cleaner (Advanced Database Cleaner – Optimize & Clean Database to Speed Up Site Performance), or phpMyAdmin.
Proceed at your own risk, or find someone to do it for you.
George is still wondering what he is doing here….
