Welcome to WebmasterWorld Guest from 107.20.108.136

Forum Moderators: rogerd & travelin cat

Message Too Old, No Replies

Tips on Cleaning Up Wordpress Database Tables

I'm in the housecleaning process and could use some guidance

     
4:15 pm on Sep 5, 2012 (gmt 0)

Moderator

WebmasterWorld Administrator webwork is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 2, 2003
posts:7847
votes: 23


I'm in the process of re-launching several Wordpress sites with the goal of improving speed/security and growing the sites.

Most of my WP sites used plugins that I eventually chose deactivated and deleted due to lack of maintenance of the plugin, etc.

Unfortunately plugin deletion often leaves a lot of rot/detritus - useless fields - in the WP database tables AND I am concerned that the extra db fields may make future forensics, redesign or upgrade efforts problematic.

FWIW, the WP sites aren't that large ~100 to 200 pages/posts, so I've even considered entirely fresh installs and using backups of the text, images, page meta data to perform a rebuild from scratch. Maybe this is the best way to go . . but . .

I've been able to remove many unnecessary fields by simply examining the "field names" in the WP "Options Table" since often, but not always, the field has a prefix that identifies its association with a plugin - such as aioso or wptc.

The problem with cleaning the Options Table is the lack of uniformity in identifying which field was added by which plugin. (Hint. Hint . . to Wordpress coding authorities.) Not all have a prefix.

I've been doing a bit of Options Table "DNA analysis" by Goggling uncertain field names in the Options table but that sometimes leads nowhere.

I have referred to the WP Codex and my own fresh WP installs - ones without plugins - to help clarity which fields are essential or "core", but that still doesn’t tell which "other fields" are nonessential. Why? Because there are still other active plugins, usually 4 or 5.

I guess I could do 5 fresh WP installs, add 1 plugin to each fresh install, and build a list of the Options Tables fields they add. THEN, IFF I was a database pro, I could probably write a SQL query that could "look for these fields" (from my list of needed fields) and "delete all the rest". (Any database pros out there? Anyone know the query that would execute that idea?)

It would be REALLY NICE if a) each plugin had a text file identifying exactly what fields the plugin added; or, b) if there was a repository of the fields added by each plugin. Of course, there would probably be issues of fields being added or dropped as the plugins were updated. Argh.

QUESTIONS:

1. Is there a slice of PHP/code that I can look for in any plugin that would help me identify what fields the plugin is inserting or has inserted? I'm assuming there is a usual or common routine/process/lines of code, in the plugin installation process, that instantiates a procedure/process for naming and creating fields in the WP options table - and in that chunk of the install process - there is probably an easy way for me to "spot" the names of the fields being created in the Options Table.

2. Is anyone aware of repositories of Options Tables names, beyond those in the basic WP install?

3. When WP is upgraded BY WP do the installs always include deletion of "no longer functioning" Options Tables fields? Or, do I also have to go looking for the names of "old, no longer used WP-core fields that might still exist in the Options Table"?

4. I've been researching whether there's any way to cross-reference or correlate fields in the Options Table with any other tables or fields in the WP core database. In other words, IF other tables are "written to" as a result of the creation of fields in the WP Options Table. IF such "writing to other tables" takes place it would make sense to consider deleting fields in the other tables once the Options Table field is deleted. Thoughts?

I just would prefer to remove as much unnecessary crap as possible, before some "genius" :P discovers a way to take out the site by exploiting the trash in the tables. So, I'm looking to maximize my housecleaning effort.
4:21 pm on Sept 5, 2012 (gmt 0)

Preferred Member

10+ Year Member

joined:May 8, 2005
posts:408
votes: 0


Could you export your content into a fresh install of WP?
12:28 am on Sept 6, 2012 (gmt 0)

Moderator

WebmasterWorld Administrator webwork is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 2, 2003
posts:7847
votes: 23


I'm not 100% certain how to achieve "exactly that" - articles/text, URLs structure, page titles/metas, images with the same links - though I imagine I could run a smallish test.

I'm guess this would amount to a "standard backup and install" of the kind that one does if ones site either blows up or gets hacked? Not exactly certain how to execute this. Advice?
3:05 am on Sept 6, 2012 (gmt 0)

Preferred Member

10+ Year Member

joined:May 8, 2005
posts:408
votes: 0


I would poke around for "WP import/export", there seems to be quite a few tools or if you want to get your hands dirty some tutorials using phpmyadmin.
12:31 pm on Sept 10, 2012 (gmt 0)

Preferred Member

joined:June 10, 2011
posts: 521
votes: 0


Try wp-dbmanager.
12:40 pm on Sept 10, 2012 (gmt 0)

Preferred Member

5+ Year Member

joined:Nov 16, 2010
posts:533
votes: 0


they have the wordpress import/export plugin which exports all posts, pages, comments to xml file which can then be re imported by wordpress intoa new install with same everything insofar as you've set up the perma urls site url the same,

its the default plugin wordpress will try to install if you simply go to tools, export
7:13 am on Oct 10, 2012 (gmt 0)

Administrator

WebmasterWorld Administrator phranque is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Aug 10, 2004
posts:10550
votes: 10


this might be a helpful resource.

Database Description « WordPress Codex:
http://codex.wordpress.org/Database_Description [codex.wordpress.org]

how many sites are you talking about?
9:51 am on Oct 10, 2012 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Feb 12, 2006
posts:2532
votes: 36


if you've still got the plugins downloaded somewhere, then you could just look through the source code and see what fields or tables they added.
plugins usually only have 1 or 2 files, so it shouldn't take too long.
12:58 am on Oct 12, 2012 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member lorax is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Mar 31, 2002
posts:7575
votes: 0


Darn you phranque - I was just about to post that link.

Sorry Webwork, there is no easy way to do it but the most reliable is to use the diagram and resource phranque posted as your guide. Make a db backup before you begin then cut out the chaff.
2:42 am on Oct 18, 2012 (gmt 0)

New User

10+ Year Member

joined:Apr 27, 2005
posts: 22
votes: 0


1. Is there a slice of PHP/code that I can look for in any plugin that would help me identify what fields the plugin is inserting or has inserted?


You can look for "update_option" and "add_option" in the plugin code. Those are the standard ways for WP plugins to insert values into the options table. Hopefully no plugins are doing direct inserts.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members