Elgg stores almost everything as an entity, and that single design decision shapes every database problem you will eventually face. A user, a blog post, a comment, a group, a friendship, and a river activity item are all rows in elgg_entities, with their descriptive data scattered across elgg_metadata, elgg_annotations, elgg_relationships, and elgg_metastrings. One visible object on the site maps to dozens of underlying rows. When a site runs for a year or two, those satellite tables grow far faster than the entity table itself, and the join-heavy queries Elgg generates start to drag. On a managed shared account you cannot touch my.cnf, run OPTIMIZE as an administrator, or tune the global buffer pool, so maintenance has to happen entirely through phpMyAdmin, File Manager, and the Elgg admin dashboard. The good news is that the most damaging issues on Elgg are almost always application-level debris, not server configuration, and those you can reach from your own account.

Diagnosing where the bloat actually lives

Before changing anything, find out which tables are heavy and which are merely large because they should be. Open phpMyAdmin from cPanel (Databases → phpMyAdmin) or DirectAdmin (Account Manager → phpMyAdmin), select your Elgg database on the left, and click the Structure tab. The table list shows row counts and the size of each table. On a neglected Elgg site the pattern is predictable: elgg_metastrings, elgg_metadata, and elgg_annotations dominate, often many times larger than elgg_entities.

To rank tables by data and index size, run this read-only query in the SQL tab. Replace your_db with your actual database name, which cPanel prefixes with your account username (for example cpuser_elgg):

SELECT table_name,
       table_rows,
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'your_db'
ORDER BY (data_length + index_length) DESC;

A revealing symptom of Elgg bloat is a elgg_metastrings table with far more rows than the content warrants. Metastrings deduplicates text values, but older Elgg versions and some plugins created duplicate or orphaned strings that never get cleaned. Check how many metastrings are no longer referenced by any metadata or annotation:

SELECT COUNT(*) AS orphaned_metastrings
FROM elgg_metastrings ms
LEFT JOIN elgg_metadata md ON md.value_id = ms.id
LEFT JOIN elgg_annotations an ON an.value_id = ms.id
WHERE md.id IS NULL AND an.id IS NULL;

Also look for the single biggest source of silent growth: the activity river and the system log. The elgg_river table records every action on the site, and elgg_metadata rows tied to deleted entities frequently linger. Count metadata whose owning entity no longer exists:

SELECT COUNT(*) AS orphaned_metadata
FROM elgg_metadata md
LEFT JOIN elgg_entities e ON e.guid = md.entity_guid
WHERE e.guid IS NULL;

If these orphan counts run into the tens of thousands, that is your bloat. Note the figures somewhere before you act, because the same queries confirm the cleanup worked afterward.

Safe cleanup through the admin panel and phpMyAdmin

Always export a backup first. In phpMyAdmin select the database, click Export, choose the Custom method, select gzipped output, and download the .sql.gz file. For larger databases that time out in the browser, use cPanel's Files → Backup → Download a MySQL Database Backup instead, which runs server-side and avoids PHP execution limits. Keep this file until you have verified the site works after maintenance.

Elgg provides its own maintenance tooling that is safer than raw SQL because it respects foreign relationships. Log into the admin dashboard and open Administration → Configure → Settings → Advanced, then look for the Upgrade and Database maintenance options. Running the garbage collector and the database validation routines from here removes orphaned metastrings and disabled entities using Elgg's own logic. In Elgg 3.x and 4.x this surfaces as pending upgrades and a cleanup task under Administration → Upgrades; run each listed item to completion rather than skipping ahead.

For the activity river, trimming history is the highest-impact cleanup with the lowest risk, because river entries are regenerated by ongoing activity and old items are rarely viewed. Remove river entries older than a chosen date directly in phpMyAdmin:

DELETE FROM elgg_river
WHERE posted < UNIX_TIMESTAMP('2025-01-01 00:00:00');

Delete in modest batches on a busy site to avoid holding locks too long. Add LIMIT 5000 to the statement and run it repeatedly until it affects zero rows. After trimming, reclaim the freed space one table at a time with the Operations tab: open each large table, scroll to Table maintenance, and click Optimize table. On InnoDB this rebuilds the table and its indexes, shrinking the physical file and refreshing statistics the query planner relies on. Run it on elgg_metastrings, elgg_metadata, elgg_annotations, and elgg_river after the deletions.

Tracing slow queries and checking indexes

When pages stall rather than error out, the cause is usually a query scanning far more rows than it returns. You cannot enable the server slow-query log without root, but you can see the pain inside PHP. Turn on Elgg's own logging by setting the log level to Notice or enabling the developer tools plugin under Administration → Plugins, then watch your account's error_log. That file appears in the directory where the slow request ran — check public_html/error_log or the specific subfolder via File Manager — and PHP timeout warnings there point you to the offending page.

To confirm an index problem, take a representative Elgg query and prefix it with EXPLAIN in phpMyAdmin. A healthy Elgg metadata lookup should use the index on entity_guid; if EXPLAIN reports type: ALL and a large rows value with no key chosen, statistics are stale or an index is missing. Re-running Optimize table refreshes statistics, which often restores the planner's index choice without any schema change. Verify the core indexes still exist with:

SHOW INDEX FROM elgg_metadata;
SHOW INDEX FROM elgg_entities;

Elgg ships correct indexes out of the box, so a missing one almost always means an interrupted upgrade. If the standard entity_guid or name_id/value_id indexes are absent, do not invent your own — re-run the Elgg upgrade from Administration → Upgrades, which restores the schema the application expects. If you are weighing whether these slowdowns justify a bigger plan, the companion article on finding the real bottleneck before upgrading covers how to separate database debt from genuine resource ceilings.

Repairing broken tables and fixing connection errors

A "Table is marked as crashed" or "database error" message on Elgg usually follows a disk-full event or an abrupt MySQL restart during a write. From phpMyAdmin, select the affected table, open the Operations tab, and run Check table followed by Repair table. InnoDB tables, which Elgg uses by default, cannot be repaired with these commands the way MyISAM can; for InnoDB corruption the practical account-level fix is to restore that table from your export — drop the broken table and re-import just that table from the backup .sql file through the Import tab.

Connection errors ("Could not establish a database connection") are a separate class and rarely mean the database is damaged. Open elgg-config/settings.php or the settings stored under your Elgg config in File Manager and confirm the dbhost, dbuser, dbname, and dbpass match exactly what cPanel shows under MySQL Databases. On shared hosting dbhost should be localhost, not an IP. If credentials are correct but connections fail intermittently under load, you are hitting the account's max_user_connections limit — reduce it by enabling Elgg's data caching and query cache under Administration → Configure → Settings → Advanced so repeat page loads stop opening fresh connections. Set memory_limit and a sane max_execution_time in a .user.ini file in public_html to let heavy maintenance pages finish rather than dying mid-query and leaving locks behind. Consistent, scheduled runs of the river trim and table optimization keep the entity schema lean enough that none of these failures recur.