How SilverStripe Structures Its Database (And Why It Bloats)

SilverStripe maps every PHP DataObject to one or more MySQL/MariaDB tables, and understanding that mapping is the starting point for any maintenance work. A single class like SiteTree is spread across a base table (SiteTree) plus subclass tables (Page, ErrorPage, and so on) joined by a shared ID. On top of that, the versioning system behind the CMS keeps a complete history of every published and draft record in parallel _Live and _Versions tables. So editing one page can touch SiteTree, SiteTree_Live, SiteTree_Versions, Page, Page_Live, and Page_Versions simultaneously.

That architecture is excellent for rollback and audit trails, but it is the primary reason SilverStripe databases grow faster than editors expect. Every save appends a new row to the _Versions tables rather than overwriting. A content-heavy site edited daily for a year can accumulate tens of thousands of version rows, and those rows carry the full serialized content of each field, including large HTML blobs. The visible site uses only the latest _Live row, yet the historical rows sit in the same table consuming disk and inode allocation on your shared account.

Beyond versioning, bloat creeps in from a handful of predictable sources. The LoginAttempt table records every authentication event and is never trimmed automatically. If you run the queued-jobs module, the QueuedJobDescriptor table retains completed and broken jobs. Form submissions from the userforms module stack up in SubmittedForm and SubmittedFormField. Session data and the CacheStore tables can also balloon. None of this is a defect, but on a shared plan where your disk quota and MySQL table counts are capped, unmanaged growth leads to slow backups, failed exports, and eventually write errors when the account hits its limit.

Before changing anything, get an honest picture of the problem from phpMyAdmin. Open cPanel at /cpanel (or DirectAdmin and choose phpMyAdmin under Account Manager), select your SilverStripe database, and run a diagnostic query against the schema. In the SQL tab, paste the following to rank tables by size:

SELECT table_name,
       ROUND((data_length + index_length) / 1048576, 1) AS size_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 25;

This read-only query works perfectly under an unprivileged user because it only inspects information_schema for your own database. The output almost always shows the _Versions tables, LoginAttempt, and queued-job tables at the top. Record these numbers so you can measure improvement after cleanup.

Repairing Broken Tables and Resolving Connection Errors

A corrupt table usually announces itself with a 500 error page and a line in your SilverStripe log along the lines of Table './dbname/SiteTree_Live' is marked as crashed. On shared hosting you cannot run myisamchk or restart the database daemon, but phpMyAdmin exposes safe equivalents. Select the affected database, tick the checkbox beside the crashed table in the structure list, and choose Repair table from the "With selected" dropdown. For MyISAM tables this runs a standard REPAIR TABLE; for InnoDB tables the same menu offers Check table and Optimize table, which is the supported path since InnoDB does not use the older repair mechanism.

If a table is InnoDB and genuinely damaged, the only account-level recovery is to restore it from a backup export. Keep a current dump by using cPanel's phpMyAdmin → Export with the Custom method, selecting only the broken table, and choosing the SQL format. You can then drop the damaged table and re-import the clean copy. After any restore, always run the SilverStripe schema rebuild described in the next section so the ORM confirms the structure matches its expectations.

Connection errors are a separate failure mode and show up as Couldn't connect to database or Access denied for user during a page load or a dev/build. These come from credentials in your environment file, not from the database contents. SilverStripe reads connection details from the .env file in your web root or from _config.php on older sites. Open the File Manager in cPanel, navigate to the document root (commonly /home/USER/public_html or a subfolder for addon domains), and verify the values:

SS_DATABASE_SERVER="localhost"
SS_DATABASE_NAME="user_ssdb"
SS_DATABASE_USERNAME="user_ssuser"
SS_DATABASE_PASSWORD="your-password"

On Hostiso's cPanel and DirectAdmin accounts the database host is almost always localhost, and both the database name and username carry your account prefix (for example user_). If you recently changed the password in MySQL Databases, the .env value must match exactly. A frequent cause of intermittent connection drops is exhausting the account's max_user_connections because of slow queries holding connections open, which leads directly into query tuning.

Finding Slow Queries and Fixing Inefficient Indexes

SilverStripe ships with a built-in profiler that needs no server access. Append ?showqueries=1 to any front-end URL while logged in as an administrator, or run a controller in flush mode, and the framework prints every SQL statement with its execution time. For a cleaner view, the dev/ area at https://yourdomain.com/dev/ lists developer tasks, and enabling SS_ENVIRONMENT_TYPE="dev" in .env temporarily surfaces the full query log in the debug panel. Watch for queries that scan the large _Versions tables or that filter on an unindexed column.

When you identify a slow filter, confirm the index situation in phpMyAdmin with EXPLAIN. Paste the offending query prefixed with EXPLAIN into the SQL tab; a type of ALL with a high rows count means a full table scan. SilverStripe defines indexes in PHP via the $indexes array on each DataObject, and you should add them there rather than directly in SQL so they survive future schema rebuilds. If you cannot edit module code, a targeted index added through phpMyAdmin's Structure → Index tool is acceptable as a stopgap, for example indexing SubmittedFormField.ParentID when form reports run slowly.

Reclaim space and refresh index statistics by running Optimize table from the phpMyAdmin "With selected" menu on the bloated tables you identified earlier. This is the safe, account-level equivalent of defragmentation and often shrinks an over-grown _Versions table noticeably once old rows are removed. For sites that rely heavily on caching and PHP tuning to mask database pressure, the general approach mirrors what we cover for other platforms in MODX Revolution performance and caching.

Pruning Unnecessary Data and Preventing Recurrence

SilverStripe provides sanctioned tasks for trimming history instead of deleting rows by hand. The most important is the versioning cleanup, available at https://yourdomain.com/dev/tasks/ where the task list appears. Running dev/tasks/VersionedCleanupTask removes old draft versions while preserving published states, dramatically cutting _Versions table size on established sites. Before any pruning task, take an export from phpMyAdmin so you have a rollback point.

For the other chronic offenders, carefully scoped SQL in phpMyAdmin is appropriate because these tables carry no structural dependencies. Clear stale login records with DELETE FROM LoginAttempt WHERE Created < DATE_SUB(NOW(), INTERVAL 30 DAY); and remove finished queued jobs with DELETE FROM QueuedJobDescriptor WHERE JobStatus IN ('Complete','Broken') AND Created < DATE_SUB(NOW(), INTERVAL 14 DAY);. Run these during low traffic, then Optimize table each one afterward.

Every time you change the schema, restore a table, or add an index through code, finish by visiting https://yourdomain.com/dev/build?flush=1 as an administrator. This rebuilds the database structure to match the SilverStripe class definitions, recreates any missing indexes, and clears the manifest cache. Set a monthly reminder to re-run your information_schema size query and the VersionedCleanupTask so the database stays lean within your shared plan's limits, and your backups stay fast enough to complete reliably.