Joomla Database Maintenance — Database Tools, Cache Clearing and System Optimization

In this tutorial, you'll learn how to maintain a healthy Joomla database — using the Database Fix tool to repair schema issues, clearing cache and expired sessions, checking extension database status, using System Information for diagnostics, and performing regular maintenance tasks.

What You'll Learn

• Using the Database Fix tool to repair schema differences
• Clearing cache groups and expired cache entries
• Using Global Check-in to unlock stuck records
• Checking directory permissions and PHP configuration
• Managing extension database schemas
• Configuring mail and testing SMTP settings
• Scheduling regular maintenance tasks

Why It Matters

Joomla is built on MySQL, and the database is the heart of your site. Over time, databases accumulate schema drift (missing tables from extension updates), orphaned cache entries, locked records from interrupted sessions, and expired session data. These issues slow down your site, cause PHP errors, and can even break functionality. Regular database maintenance prevents these problems. Joomla provides built-in tools for most maintenance tasks — you do not need to be a database administrator to keep your site healthy.

Real-World Use

A Joomla site administrator notices error messages after updating a third-party extension: "Table 'jos_xyz_items' does not exist." Going to System > Database shows the extension's database schema is outdated with 5 missing tables. Clicking Fix updates the schema instantly. The administrator also notices the site has 40,000 expired session records and 12,000 orphaned cache entries. Clearing expired cache and sessions reduces the database size by 60 MB and improves page load time by 30%. Monthly maintenance keeps the site running smoothly.

Learning Path

flowchart LR
  A["Joomla API"] --> B["Database Maintenance"]
  B --> C["Go-Live Checklist"]

  classDef current fill:#38bdf8,color:#0f172a,stroke-width:2px
  class B current

Database Tab in System

The Database tool shows the schema status of all installed extensions.

1. Go to System > Database
2. The page lists all extensions and their database schema version

The display shows:

What It Means

• OK — The extension's database matches its code version. No action needed.
• Update Needed — The extension has been updated but the database schema has not. Click Fix to update.
• Missing — Database tables or columns are missing. Click Fix to create them.

Database Fix

When you see an extension with "Update Needed" or "Missing" status:

1. Check the checkbox next to the affected extension
2. Click the Fix button in the toolbar

Joomla performs these actions:

• Creates missing tables
• Adds missing columns
• Updates schema version numbers
• Renames deprecated columns or tables

When to Run Database Fix

• After updating any extension
• After updating Joomla core
• After restoring a backup from a different Joomla version
• When you see database-related PHP errors

// Typical database error that requires Fix
// Table 'jos_xxxx' doesn't exist
// Column 'yyyy' not found in table 'jos_zzzz'

Cache Clearing

Cache groups store different types of cached data. Clearing them forces Joomla to regenerate the content.

Cache Groups

Clearing Cache

1. Go to System > Clear Cache
2. Select the cache groups you want to clear
3. Click Delete

Deleting Expired Cache

Click Delete Expired to remove only cache entries past their expiration time. This is faster and less disruptive than deleting all cache.

When to Clear Cache

• After publishing new articles
• After changing module settings
• After updating extensions
• After modifying template files
• When you make changes that do not appear on the frontend

Global Check-In

When a user edits an article, module, or category and does not log out properly (browser crash, session timeout), the record remains locked. Global Check-In unlocks all locked records.

1. Go to System > Global Check-In
2. The page shows all locked items:

3. Select the items to check in
4. Click Check In in the toolbar

When to Use Global Check-In

• An editor reports they cannot edit an article because "another user is editing it"
• After a server crash or unexpected restart
• When multiple editors share content management

System Information

The System Information page provides comprehensive details about your server and Joomla configuration.

1. Go to System > System Information

System Information Tabs

System tab:

PHP Configuration tab:

Directory Permissions tab:

Lists all Joomla directories and their writable status:

Directory: /var/www/joomla/
  images/       → Writable
  cache/        → Writable
  tmp/          → Writable
  logs/         → Writable
  administrator/logs/ → Writable

Directory Permissions Check

Joomla needs certain directories to be writable by the web server:

# Set correct permissions
chmod 755 /path/to/joomla/
chmod 755 /path/to/joomla/cache/
chmod 755 /path/to/joomla/tmp/
chmod 755 /path/to/joomla/logs/
chmod 755 /path/to/joomla/images/
chmod 755 /path/to/joomla/administrator/logs/

# If needed, set ownership to web server user
chown -R www-data:www-data /path/to/joomla/

PHP Information

The PHP Information tab in System Information shows all PHP configuration settings. This is useful for:

• Checking if required PHP extensions are loaded (mysqli, gd, curl, json, xml, mbstring)
• Verifying memory limit and upload size
• Debugging PHP configuration issues

Required PHP Extensions

# List all PHP extensions loaded
php -m

# Required for Joomla 5:
# mysqli, gd, curl, json, xml, mbstring, zip, dom, openssl

Expired Sessions

Joomla stores user sessions in the #__session table. Over time, this table accumulates records from users who have logged out or whose sessions expired.

-- Check session table size
SELECT COUNT(*) FROM jos_session;

-- Check for expired sessions
SELECT COUNT(*) FROM jos_session WHERE time < UNIX_TIMESTAMP() - 3600;

Clearing Expired Sessions

Joomla automatically clears expired sessions periodically. You can also clear them by going to System > Clear Cache and clearing any cache group — session cleanup runs as part of this Process.

For manual cleanup:

DELETE FROM jos_session WHERE time < UNIX_TIMESTAMP() - 1440;

Module Manager Database Tab

When you install or update modules, the Database tab in Extensions > Manage shows the schema status.

1. Go to Extensions > Manage > Database
2. This shows the same information as System > Database but in the Extensions context
3. Use this view when troubleshooting extension-specific database issues

Extension Database Status

1. Go to Extensions > Manage > Database
2. Check that all extensions show "Database version matches extension version"
3. If any show "Database update needed", click the Fix button

Mail Configuration Test

Testing SMTP Settings

1. Go to System > Global Configuration > Server
2. Configure mail settings:

3. Click Send Test Mail
4. If the test email arrives, your mail configuration works

Common Mail Issues

• SMTP port blocked by hosting provider (try 587 or 465)
• Authentication failure (check username/password)
• SSL certificate issues (try STARTTLS instead of SSL)

Optimizing Database Tables

Over time, database tables become fragmented. You can optimize them via phpMyAdmin or command line:

mysqlcheck -u root -p --optimize --all-databases

# Or for a specific database
mysqlcheck -u root -p --optimize joomla_db

Dashboard Maintenance

The administrator dashboard shows modules for:

• Latest Articles — review recently published content
• Logged-in Users — see who is currently editing
• Popular Articles — identify high-traffic content
• Recently Added Modules — review new modules

Use these dashboard modules to stay on top of content changes.

Common Mistakes

1. Ignoring the Database tab warnings: A yellow "Update Needed" status is not urgent, but delaying it means schema differences accumulate. Fix them promptly after each update.

2. Clearing all cache too frequently: Clearing cache every time you make a change forces the site to regenerate everything. Use "Delete Expired" instead for routine maintenance, and only delete all cache when necessary.

3. Forgetting Global Check-In: Users who close their browser without logging out leave locked records. Other editors cannot edit those items. Run Global Check-In weekly or when someone reports a locked item.

4. Not checking directory permissions after server Migration: Directory permissions often change when moving servers. After any migration, check System Information > Directory Permissions to ensure all required directories are writable.

5. Letting sessions table grow unchecked: On high-traffic sites, the sessions table can grow to millions of rows, slowing down login and authentication. Clear expired sessions regularly or set up a cron job.

Practice Questions

1. What information does the Database tab in System show, and when should you click the Fix button? Answer: It shows each extension's schema version versus the expected version. Click Fix when an extension shows "Update Needed" or "Missing" status — this creates missing tables, adds missing columns, and updates the schema version.

2. What is Global Check-In used for, and when should you run it? Answer: Global Check-In unlocks records (articles, modules, categories, menu items) that are locked by users who did not log out properly. Run it after a server crash, when an editor reports they cannot edit an item, or as a weekly maintenance task.

3. What PHP settings should you check in System Information, and what are the recommended values? Answer: Check memory_limit (recommended 128M+), upload_max_filesize (10M+), post_max_size (20M+), max_execution_time (120+). These affect Joomla's ability to process large uploads and complex pages.

4. Challenge: Perform a complete health check on a Joomla site. Check the Database tab and fix any issues. Clear expired cache and sessions. Run Global Check-In. Review System Information for PHP configuration issues. Check directory permissions. Test mail configuration. Optimize database tables. Write a health report with pre- and post-maintenance metrics (database size, cache size, number of locked records, page load time).

FAQ

Mini Project

Your task is to perform a complete database health check and maintenance routine on a Joomla site.

1. Go to System > Database and identify any extensions needing schema updates. Fix them.
2. Go to System > Clear Cache and delete all expired cache entries. Note how many entries were cleared.
3. Go to System > Global Check-In and check in any locked records.
4. Go to System > System Information and verify:
   • PHP version meets Joomla 5 requirements
   • memory_limit is at least 128M
   • All required PHP extensions are loaded
   • All directories are writable
5. Test your mail configuration (use a real SMTP or a local mail catcher)
6. Optimize your database tables using phpMyAdmin or command line
7. Set up a recurring monthly reminder to repeat steps 1-6

Document your findings with before-and-after metrics (database size, number of locked records, cache entries cleared).

What's Next

Now that your database is healthy, prepare your site for production:

Continue to Lesson 40: Joomla Go-Live Checklist — Pre-launch, hardening, and deployment.

Related lessons:

• {{< ilink "Joomla" "Joomla Backups" }} — Take a backup before major maintenance
• {{< ilink "Joomla" "Joomla Caching" }} — Performance optimization after maintenance