Help to understand which tables can be deleted...

Hello Support-Team,

now were obviously the issues we experienced with our Multi-DB installation are mostly eliminated its time to clean out all db's by deleting empty tables which are leftovers from deinstalled plugins.

As I am not an expert like you guys I really do appreciate if you could give me some explanation (please in plain english only) to avoid any mistakes.

... As I can't attach at this point any screenshots I just post this request and continue by using the opportunity to reply on next screen...

  • Klaus

    1) Please view the attached screenshot and let me know the meaning of the various columns of

    - Data Length
    - Index Length
    - Auto Increment
    - Rows

    Some of them has a value at all columns, some not...

    How do I figure out which of those tables are empty in order to delete them without getting in trouble?

    2) As I have many db's (in total 256 plus .global) with each lots of tables in it, would you know is there any plugin for multi-db's available which could do this intended "cleaning out" procedure automatically? Please let me know because if so that would be so wonderful... and of course minimize the risk that I could delete by accident a table to much.

    Looking forward to hear from you guys again.
    Many thanks in advance.
    Regards Prinz

  • Michelle Shull

    Hello, Prinz

    Hope you're well on this lovely Saturday.

    Okay. So we're getting into the nitty gritty of your databases now. First off, glad to hear your other issue was resolved.

    Before we begin this essay, what's the first rule of database?
    ALWAYS TAKE A BACKUP FIRST. :slight_smile:

    To start, there is a tool built right into PHPMyAdmin which will allow you to optimize your databases. What optimizing does is remove any overhead, which is extra bits (technically, bytes) of data that you don't need, like post revisions, auto saves, and deleted items. To optimize in PHPMyAdmin, just select all the tables in a database, and there will be a drop-down at the bottom that will let you choose between bulk actions. You want optimize, if all your tables are select. Don't accidentally hit "Drop" which will wipe the whole thing. Never hit drop unless you really mean it and you've triple checked everything to make sure you're not dropping something you need.

    1) Please view the attached screenshot and let me know the meaning of the various columns of

    - Data Length
    - Index Length
    - Auto Increment
    - Rows

    Data length:

    Prior to MySQL 5.1.12, MySQL Cluster allocated storage for variable-width columns in 10-page extents of 32 kilobytes each; thus, the DATA_LENGTH for such columns was reported in increments of 320 KB. Beginning with MySQL 5.1.12, the DATA_LENGTH column reflects the true amount of storage for variable-width columns of NDB tables.

    This is a limit on how much data can be stored within the table, roughly. That's somewhat over simplified, so I can give you a more in depth explanation of how data treated by a database, if you want it. It's not riveting stuff, unless you've got an interest in understanding how a computer can store, process and call data.

    Index length: This is the length of the index (or data stored here) in bytes.

    Auto Increment: This is used to step up the ID number when a new record is created in this table. So, for example, the members table would check the last added member's ID number and add 1 to it to create the newest member's ID number.

    Rows: This is just the number of rows in the table. Some tables only need one row, some need several, it depends on what data is being stored and how it's to be used.

    How do I figure out which of those tables are empty in order to delete them without getting in trouble?

    To be safe, you should only remove tables that you know are part of plugins you aren't using and will not use again in the future. Databases are extremely complex - it takes years of study to fully understand how everything interconnects. Do not rely on how much data is stored as your decision making tool here, as some critical tables may in fact not show data here, and several of the old plugin tables will have lots of data stored in them that you don't need anymore.

    The easiest way to do this is to look at the database table names. Any plugin worth its salt that creates new tables will use a good prefix to help users and other developers find the associated tables. Our membership, for example, uses a prefix of m_. Not all plugins are going to be this obvious, however, so it may be worth your time to do a little googling if you find a table with a prefix you don't recognize.

    2) As I have many db's (in total 256 plus .global) with each lots of tables in it, would you know is there any plugin for multi-db's available which could do this intended "cleaning out" procedure automatically? Please let me know because if so that would be so wonderful... and of course minimize the risk that I could delete by accident a table to much.

    There's a tool I used to use on single sites, but I'm quite sure it wouldn't work on either a multisite or a Multi-DB install, just because things get increasingly more complex.

    I'll say again that there is no shame at all in hiring a db expert to help you through this. Database is a big deal, and little mistakes can become big mistakes if you accidentally drop the wrong table. An expert will never need to guess, and they'll be available to solely help you, which is a little harder for us to do on the forums when we're trying to help everyone.

    Hope this helps a little. I know you'd rather not get help from me at all.

  • Michelle Shull

    Heya Prinz!

    Going by prefix is the best option for someone who's not already a database expert. An expert will be able to parse what they see a lot faster than a novice, or even an intermediate database pro. The big advantage to a pro is speed, once they figure out what's happening in your system, they'll be able to complete the clean up lightning fast with some well structured queries.

    I've always been a big DIYer when it comes to my own sites, even before I did support for a living, and this is a job I'd hire out in a heartbeat. The database is the brain of your site, it's never something to approach without a good deal of caution and confidence in your skills. There's a good reason why database experts are usually more expensive than a more generalized developer. :slight_smile:

    And in your case, it's even more complex, because you've got 256 databases. If you do hire out, don't go for a generalist. Pick someone who specializes in database, with a lot of experience specifically working with WordPress databases, preferably multiDB installs if possible. Then prefixes aren't going to be a problem, they'll have dozens or hundreds of similar projects under their belt, it won't be a hunt-and-peck like it would for you or I.

    Ultimately, a pro is going to be able to complete this job in a few hours with some exquisitely written queries, where it would take me or one of my colleagues a few days to search and destroy (if we could write exquisite db queries, we'd probably be doing that for a living. : p ) , and it would take you a while longer as a relative newbie.

    Thanks, Prinz, happy to answer any other questions here.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.