---
title: "WordPress Optimization: Database Edition"
url: https://www.geekcorelab.cn/en-US/post/database-optimization-for-wordpress
updated: 2026-11-01T13:20:59.354506Z
author: "hhjmk"
summary: "MySQL 的 autoload 属性控制表是否在启动时自动加载，设置为 1 可提高查询速度但占用更多内存。大型 WordPress 站点应关注 wp-postmeta 表的大小，可使用 Database Cleaner 插件管理autoload 设置。减少废表数量需谨慎删除插件表，插件可显示表用途。清理文章修订可删除无用数据，限制修订数量。整理碎片需运行 MySQL 查询优化表。选择数据库版本时，MariaDB 在大型查询中表现优于 MySQL。MySQL 读写分离可提升性能，建议主从架构，使用插件或云服务实现，需注意兼容性测试和服务器部署。"
---

# WordPress Optimization: Database Edition

Optimizing AutoLoad
-----------

The autoload attribute of a MySQL table controls whether the table is automatically loaded into memory when the database starts.

- **When set to 1**, the table is automatically loaded when the database starts, which can speed up frequently queried tables, but it also consumes memory space.
- **When set to 0**, the table is not automatically loaded, which can save memory space, but it needs to be loaded on the first query, so it is slower.

For relatively large WordPress sites, be sure to check the size and row count of the “wp-postmeta” table. In general, this table may be the largest table in your database. You can use the following command to find out which table is the largest in your database, as well as how many MiB and how many rows of data this table has

```
SELECT table_name,
       (data_length + index_length) / 1024 / 1024 AS total_mib,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'wordpress'
ORDER BY (data_length + index_length) DESC
LIMIT 1;
```

Its output may look similar to this: table\_name refers to the table name of the largest table, total\_mib refers to the size of the largest table (MiB), and total\_rows refers to how many rows of data the largest table has

```
| table_name  | total_mib | total_rows |
| ----------- | --------- | ---------- |
| large_table | 10.24     | 100000000  |
```

If these relatively large tables are tables you frequently use, you can set them to autoload. This sacrifices some memory space in exchange for faster query speed. The memory usage depends on the size of the tables you set to autoload; in general, it will occupy an additional 10%-50% on top of the table size

We will use a simple method (I don't know the complex method) to set the autoload for WordPress options. First, you can install a plugin (highly recommended) called “**Database Cleaner**”. This plugin lets you clean the database directly within WordPress without needing to understand complex SQL query commands

![](/uploads/import-placeholder/315)
As you can see, you can directly set whether items in Options are autoloaded. If you want to disable autoload, you can uncheck the checkbox on the far right

If you don't know what this option does, you can refer to Used By and click the query icon in the Size column to see what is actually stored in this option

![](/uploads/import-placeholder/321)
For plugins that have been disabled or deleted, there will also be corresponding hints in the Used By column

![](/uploads/import-placeholder/311)
Reduce the Number of Junk Tables
------

![](/uploads/import-placeholder/9)
Still using this plugin, click the Tables section to see which tables are used by which plugins. Before deleting a table, it is recommended to carefully investigate which plugin created the table; otherwise, accidental deletion may cause strange problems

![](/uploads/import-placeholder/112)
You can use the buttons here to switch between table optimization and table deletion modes

Clean Up Post Revisions
------

![](/uploads/import-placeholder/374)
In the WordPress Core section, you can see the number of post revisions, auto-drafts, and posts in the trash. If you do not need version revisions, you can safely delete them. Of course, you can also limit the maximum number of revisions per post; once this number is exceeded, the oldest revision will be deleted when a new revision is generated

```
// 最大修订数量
define( 'WP_POST_REVISIONS', 3 );
```

Defragment
----

Run this query in the database to defragment; MySQL will rebuild the table. After execution completes, you can see that the query took 29.1617 seconds

![](/uploads/import-placeholder/217)
Reference:

[MySQL :: MySQL 8.0 Reference Manual :: 17.11.4 Defragmenting a Table](https://dev.mysql.com/doc/refman/8.0/en/innodb-file-defragmenting.html)

Choose the Correct Database Version
---------

As is well known, the currently major versions of MySQL in use are 5.7 and 8.x. You can refer to online performance tests when choosing a database. In addition, if you are running a large website with WordPress, you can try MariaDB, because MariaDB has a slight advantage in queries on large databases

When querying large amounts of data with indexes, MariaDB's query speed is indeed faster than MySQL's

Item\\DatabaseMySQLMariaDBQuery max value of time field0.006 seconds0.001 secondsFilter time field and sort0.398 seconds0.020 secondsQuery level field0.065 seconds0.049 secondsQuery message field0.003 seconds0.005 secondsMySQL Read/Write Separation
----------

In most cases, MySQL read/write separation can bring significant performance improvements. It is recommended to use a primary with multiple replicas architecture: write to the primary and read from replicas, and performance will improve considerably when the replicas are in read-only mode

![](/uploads/import-placeholder/312)
You can use some middleware to implement read/write separation without modifying application source code. If you use WordPress and are too lazy to deploy middleware, you can try the existing open-source plugin [**LudicrousDB** (github.com)](https://github.com/stuttter/ludicrousdb) to implement read/write separation. However, this method usually only works properly on certain PHP versions, so you need to test it thoroughly, and after it is stable in the development environment, gradually deploy it to production

To do this, we need to use multiple servers to form a MySQL read/write separation cluster or a sharded database cluster. Deploying multiple MySQL instances on the same server with Docker may not necessarily bring a large improvement. The best approach is to use multiple cloud servers to form a cluster. We can choose a cloud provider that supports private network interconnection to avoid certain latency caused by traffic going over the public network

![](/uploads/import-placeholder/441)
At present, some nodes of [雨云](https://www.rainyun.com/github_?s=biliwind) already support private network interconnection between multiple servers, which is very suitable for MySQL database clusters. If you want, you can also set up a Redis cluster along the way. If you want to try it, please purchase at least 3 servers to build it, or [purchase](https://www.rainyun.com/github_) one server with a slightly higher configuration and use Docker to run multiple instances for practice
