A WordPress page is not a file. There is no about-us.html sitting on the disk waiting to be sent. Your posts, comments, settings and menus live in a database, usually MySQL or MariaDB, and each page view asks it a series of questions. A typical post page might need thirty to a hundred queries before PHP can assemble the final HTML, and a busy shop page with filters can need several hundred.
Most owners never look at the database until something goes wrong: the site slows down for no obvious reason, a restore is needed, or the host sends a polite email about resource usage. A little knowledge of how it works turns those moments from mysterious into routine. This piece explains what is in there, why size matters, what bloats it and how to look after it.
What happens on a page view
The web server hands the request to PHP, WordPress starts up, and before it can print anything it needs facts. Which theme is active? What is the site title? Which post has this URL? What are its comments, its author, its categories, its custom fields? Each of those is a query sent to the database server, which looks the answer up and sends it back. PHP then assembles the page from the replies.
That is also why a cached page is so much cheaper: it skips the right-hand half of the diagram entirely.
Tables, rows and why indexes matter
A database stores information in tables, like spreadsheets with fixed columns. WordPress creates a dozen core tables with a prefix, wp_ by default: wp_posts holds posts, pages and menu items, wp_postmeta holds extra fields attached to them, wp_options holds settings, wp_users and wp_usermeta hold accounts, and wp_comments holds comments. Plugins add more.
Finding a row in a small table is instant. In a table with a million rows, the database must either read all of them or use an index, a sorted lookup structure that works like the index at the back of a book. With an index, finding one row among a million takes a few dozen steps at most, closer to twenty than a million. Without one, the work grows with the table.
This is why a site can be fine for two years and then start crawling. Nothing changed in the code. The table crossed a size where a missing index stopped being invisible. Shops with large wp_postmeta tables, which store every product attribute as a separate row, hit this first.
What bloats it
Post revisions pile up: every save of a long article stores a full copy, so a post edited fifty times is fifty rows with the body text in each. Plugins store log entries, form submissions, analytics hits and expired temporary data ("transients") in tables that nobody cleans. A forum or shop adds rows every day. Deleted plugins often leave their tables behind, because uninstalling does not always mean cleaning up.
The sneakiest source is autoloaded options. Rows in wp_options marked autoload = 'yes' are loaded on every request, whether needed or not. A plugin that dumps a large array into one autoloaded option taxes every page on the site, including pages that never use it. Anything over a megabyte or so of autoloaded data is worth a look.
SELECT option_name, LENGTH(option_value) AS bytes
FROM wp_options
WHERE autoload = 'yes'
ORDER BY bytes DESC
LIMIT 10;Run that in phpMyAdmin or with the mysql client and the guilty party is usually obvious, often named after a plugin you stopped using a year ago. To limit revision growth for the future, add define('WP_POST_REVISIONS', 5); to wp-config.php.
Looking after it
Back it up before touching it, and test the backup. A dump is one command, and the date in the name saves arguments later:
mysqldump --single-transaction -u example_user -p example_db > example_db-$(date +%F).sqlTesting means restoring it into a scratch database and checking that the site runs against it. A dump file that has never been restored is a theory (see the backups guide). Then remove data you do not need: old revisions, expired transients, spam comments, tables belonging to plugins that are gone. Do it in small steps, and look at the site after each.
If your host offers it, enable the slow query log for a day and read what comes out. A few repeating queries usually account for most of the pain. On a VPS the settings are slow_query_log = 1 and long_query_time = 1 in the MySQL configuration; on shared hosting you may be limited to a panel feature or to asking support. When you find a slow query, put EXPLAIN in front of it and look at the "rows" and "key" columns: a large row count with an empty key means no index was used.
A worked example: the shop that got slow
A small shop with 4,000 products and three years of orders complains that category pages take six seconds, though the home page is quick. The owner has already tried a bigger hosting plan, which helped for a week. The slow query log, switched on for one afternoon, shows the same query repeating hundreds of times: a lookup in wp_postmeta filtering by a custom field a filtering plugin added.
Putting EXPLAIN in front of the query shows an estimated 2.4 million rows examined and no key chosen. The meta table has grown to that size because every order, product and variation stores a dozen fields as separate rows. Adding an index on the relevant column, which the plugin's developer documents as safe, brings the examined rows down to a few hundred, and category pages drop to under a second. The bigger plan was never the problem; the fix was a single index and an afternoon of reading a log. The lesson that carries over is the method: measure first, change one thing, measure again.
When the database says no
The dreaded "Error establishing a database connection" means PHP could not talk to the database or was refused. The causes are few. The credentials in wp-config.php no longer match, perhaps after a password reset or a migration. The database server is down or restarting. The server has run out of connections, which happens when many slow requests pile up and each holds one open. The disk is full, so MySQL cannot write. Or a table is marked as crashed after an unclean shutdown.
A quick test separates the first from the rest. From the command line on the server, try mysql -u example_user -p -h localhost example_db using the same values as the config file. If it connects, the credentials and the server are fine and the fault is elsewhere. If it says access denied, fix the password or the user's grants. If it cannot connect at all, look at the server: df -h for disk space, and the MySQL error log for crash messages. On shared hosting, the equivalent is trying the same credentials in phpMyAdmin, and contacting support if the whole server is affected.
Modern tables use the InnoDB engine, which recovers from crashes automatically in most cases. Older sites may still have MyISAM tables, which are more prone to corruption and lock the whole table during writes. Converting them is a routine job, but take a backup first and do it outside busy hours.
Caching and where the database runs
A persistent object cache such as Redis or Memcached reduces repeated questions. WordPress already caches query results within a single page view; a persistent cache keeps them between views, so the thirtieth visitor does not make the database re-answer what the first visitor asked. Page caching goes further and skips PHP altogether for anonymous visitors, as covered in the rest of this series.
| Hosting type | Where the database lives | What you can tune |
|---|---|---|
| Shared | A MySQL server shared with other accounts | Your own tables, plugins and caching; limits on connections and query time apply |
| VPS | Same machine as PHP, by default | Memory settings, slow query log, indexes; the server is yours to size |
| Dedicated | Same box, or a second one | Everything, including replicas |
| Managed WordPress | Host-managed, often separate from the web tier | Mostly your data and plugins; the host tunes the engine |
For large or busy sites, running the database on separate hardware is the next step. It stops PHP and MySQL competing for memory and lets you size each for its job, at the cost of a small network hop on every query. Before that expense, check the cheaper options: indexes, an object cache, fewer plugins.
Credentials and safety
The database user in your site's configuration file needs access only to that site's database. Do not use the all-powerful administrative account. Use a long random password, and never reuse it elsewhere. If one site on a server is hacked, a separate user per site limits the damage to that site's data.
If an attacker can read the configuration file, they can read everything in the database, so file permissions on wp-config.php matter more than most people realise. On a typical server something like chmod 640 wp-config.php (owner read and write, group read, nobody else) is appropriate, and tighter if your host allows it. Never leave a copy named wp-config.php.bak or wp-config.old in the web root: servers will happily send those out as plain text, with the password inside.
A ten-minute check
- Find your database size: in phpMyAdmin, open the database and sort tables by size, or run
SELECT table_name, ROUND((data_length+index_length)/1048576,1) AS mb FROM information_schema.tables WHERE table_schema = DATABASE() ORDER BY mb DESC LIMIT 10; - Count queries per page: a plugin such as Query Monitor on a staging copy shows the total and the slowest few.
- Check the autoload total with the query above and note anything big.
- Time a dump with
time mysqldump .... If it takes minutes, the restore will too, and you should plan for that. - Confirm file permissions with
ls -l wp-config.php.
Questions that come up
Is it safe to run an optimise plugin on my database?
Generally yes after a backup, but read what it proposes to delete. Cleaning revisions and transients is routine; deleting "orphaned" rows from a plugin you still use is not.
How big is too big?
There is no fixed number. A 200 MB database can be sluggish with a missing index while a 20 GB one runs happily on good hardware. Watch query times, not megabytes.
Why does the site slow down only at certain times?
On shared hosting, neighbours compete for the same server. On any plan, a scheduled job, a backup or a bot crawl can hold the database busy. Compare slow periods with your cron schedule and access logs.
Should I change the wp_ prefix?
It does little for security, and changing it on a live site risks breaking things. Choose a different prefix at installation if you like, but do not rely on it.