Removing obsolete database tables from your Joomla site.
Joomla uses SQL database tables to store content and data for the Joomla site and it’s extensions.
In a typical installation, you will have a database in your web hosting account for your site. The naming conventions of that hosting will vary from host to host.
Inside that database there will be tables. For example, there is a database table to track your site users called users. Since it is possible that a single database could be servicing a number of Joomla sites (Or other sites for that matter), Joomla will prepend a prefix to it’s tables. The old convention was that a table such as users would be names jos_users. Since a static naming convention provided some advantage to hackers, Joomla 3.x and up will prepend a random prefix of five characters and an underscore.
IMPORTANT – Keep in mind that Joomla and MOST EXTENSIONS will use a prefix. Some tools such as Fabrik do not create tables using the site’s default prefix.
A problem can arise, for example, when you migrate from a version of Joomla such as 2.5 to 3.x or if a developer had multiple sandbox versions of the site on the same hosting. In other words, there could be more than one Joomla installation under a particular Public_html folder. In some cases, the additional installations will use different prefixes.
The problem with obsolete tables is that they use space, can be confusing if your need to fix database/table issues, can impact performance and can increase the size of your site backups.
The first step is to go into phpmyadmin (or other tool) and look at the database. Scroll through and see if there happens to be more than one prefix in the database. CAUTION – just because there is more than one prefix, that does NOT mean that those tables can be deleted.
If there is more than one prefix you need to see which prefix your site is using. In Joomla 3.x you would go to SYSTEM -> GLOBAL CONFIGURATION -> SERVER. Look under Database Tables Prefix. The prefix will be 3-5 characters with a trailing ‘_’. That should match one of the table prefixes you see in your database. If there are other prefixes, you need to determine if they can be deleted. If the table names are those of a typical Joomla install, then you are PROBABLY safe. I have seen poorly written extensions that use a ‘retired’ prefix from the site.
If you want to be totally safe, you should back up your site and your SQL database. I use Akeeba Backup Pro to back up the site. Backing up the SQL database will vary depending on your host.
Keep in mind that deleting tables is both tedious and risky. If you are SURE that your backups are good, then you can proceed to delete the old tables.
Again, this will help with performance and simplify maintenance but there is a risk whenever you delete tables from your database – USE CAUTION.
- Accessing an external sql database from a rails app where the schema prefix differs from the schema prefix used for the app’s tables.
- Removing spaces from filenames in Joomla sites
- A quick way to clear the redirect list in Joomla
- Fabrik Database connection when changing a site domain.
- j17_finder_tokens error in Joomla Database
- Monthly maintenance for Joomla (and other) sites.
- Fabrik for joomla 2.1 to 3.x migration.
- Deleting COM_INSTALLER_TYPE_ entries in the extension manager of a Joomla 3 site