I have a big problem with the database for a site I am building. I have 3 table prefixes and abut half the entries in the database do not show up in K2 Categories as either "current" or "Trashed."

I will need to go through the database and delete rows one by one and look at all the foreign keys and delete the rows in the table pointed to by those foreign keys, being careful to make sure that the database item pointed to by the foreign key isn't referenced by another K2 items or K2 Category.

This will be a lot of work, but I can't see any better way to do this because I am not sure what is keepable and what needs to go until I look at each item.

These are the tales I need to sort through:

Structure dclvt_k2_attachments
Structure dclvt_k2_categories
Structure dclvt_k2_comments
Structure dclvt_k2_extra_fields
Structure dclvt_k2_extra_fields_groups
Structure dclvt_k2_items
Structure dclvt_k2_multiple_categories
Structure dclvt_k2_multiple_extra_field_groups
Structure dclvt_k2_rating
Structure dclvt_k2_tags
Structure dclvt_k2_tags_xref
Structure dclvt_k2_users
Structure dclvt_k2_user_groups

I also have these tables in the same database

Structure yo7vx_k2_attachments
Structure yo7vx_k2_categories
Structure yo7vx_k2_comments
Structure yo7vx_k2_extra_fields
Structure yo7vx_k2_extra_fields_groups
Structure yo7vx_k2_items
Structure yo7vx_k2_multiple_extra_field_groups
Structure yo7vx_k2_rating
Structure yo7vx_k2_tags
Structure yo7vx_k2_tags_xref
Structure yo7vx_k2_users
Structure yo7vx_k2_user_groups

Plus I have tables with table prefixes other than yo7vx and dclvt_k2 like below

Structure burpo_finder_links_terms0
Structure burpo_finder_links_terms1
Structure burpo_finder_links_terms2
Structure burpo_finder_links_terms3
Structure burpo_finder_links_terms4
Structure burpo_finder_links_terms5
Structure burpo_finder_links_terms6

Perhaps I should ask if there is a database tool that will help me in cleaning this up.

Are there any other Joomla! installations running on the same DB?
If yes do not do anything.
If no find the table suffix your site using from your configuration .php file and drop the other tables.

PS. Backup the DB first.

As always, thanks for the blazingly fast response Krikor.

Wow, this doesn't look good. I actually have four sets of tables.

I have a dev site and live site and a separate database for each. and no other Joomla installations in a sub-domain using the same database as either the live site or the dev site.

For the live site, the tables with the table prefix I designated for that database are first, followed by the tables with the other two database prefixes. For the dev site the table that has the table prefix I designated for the dev site are first, followed by the tables that have the same prefix as the tables that follow after the first tables on the live site. I use a CloudAccess feature called "Replicate" where I am able to "replicate" the entire installation of Joomla from the live site to the dev site so that I always have a dev site that is current with the live site. The replication software resolves the database table prefixes transparently. I will keep the tables with the prefix that cpanel shows as the prefix for the tables.

What is more I have both item and category entries in the database that don't appear in the K2 item manager or category manager as either current or trashed. To me this means that I have a lot of orphaned entries.

I just wanted to check with you to make sure that K2 didn't keep some additional tables with special prefixes in the database .

What I am going to do is "replicate" the live site to the dev site and then use PHPMyAdmin on the dev site to delete all the tables except for first set of tables that have the prefixes that Cpanel shows as the prefix for that MySQL database them and see what happens. If everything works OK, then I will do the same to the live site.

On the dev site I will then look to see if there are still any orphaned entries and look at the foreign keys in each table and make a diagram of the dependencies and then carefully begin deleting tables that are orphaned and go into the tables that were referenced by the foreign keys and delete the rows that refer to the tables items in the tables I just deleted; I will make lists of deleted items as I go. If everything goes well then at 2:00 am I will migrate the user table over to the dev site, back up thje live site using Akeeba Backup, and then clear the live site of all content and delete the database and then replicate the dev site to the live site and then change the prefixes back to what CPanel indicated.

Hopefully that will give me a good as new site. I think this will be easier than Installing Joomla 3.4, K2 2.6.9, all the components, modules and plugins and then migrating the template, users, items and categories tables over.

Try repairing your database's tables.

I just wanted to check with you to make sure that K2 didn't keep some additional tables with special prefixes in the database.

Nope. We always use the db's prefixes.

In general if you delete tables with different prefixes your installation will have no issues. I do not know of an extension which uses different prefixes.
Just in case do backup your db.

There were no issues with dropping the tables with different prefixes. There were some 200+ tables that were dropped and that didn't affect the operation of the website at all.

I now have some categories that appear in the k2_categories table that don't appear in the K2 Categories manager. There are also some trashed categories that appear in the K2 Categories manager that won't delete. I select them and then click "Delete" but when the delete process completes and the page updates, the categories are still there.

The two methods of repairing a database that I have found are PhpMyAdmin Tutorial: Repair and Optimize Database and a command line repair tool, 2.19.4 Rebuilding or Repairing Tables or Indexes

The database is now 1/4 the size it was originally. I ran the PHPMyAdmin Repair and Optimize tools and everything is reported as OK. Also those categories that wouldn't delete from the K2 Categories Manager are now gone and load times are now about 15% faster.

Now to see if RSForms will work with JOTCache and if JOTCache will work with JCHOptomize. Once that juggling act is complete I think we will be sailing smoothly until K2 3.0 and Joomla 3.5 is released.

I just wanted to conclude by saying thank you Krikor. Your suggesting that I Repair the database sent me off in the right direction. I still had to delete all the orphaned rows by hand, but after I finished that, Repairing and Optimizing the database finished the job. The reason they got orphaned is because they were in all the different languages supported by Joomla and K2. I uninstalled all the languages except English, but I hadn't deleted all the items and categories for those languages first. I am going to handle languages by putting each language in it's own sub-domain like fr.911truthoutreach.org and es.911truthoutreach.org, etc., so they don't impact load time.

You 're welcome Mitchell.
I think additional Joomla! installation will have more impact on your server, than a multilingual site.
If you want to speed up your site, you can try a CDN like Cloudflare or Fastly.