those sizes are nothing…  I am working on one site now with one table at 22 million rows (wp post meta)…  the wp posts table is only 700k rows…

you nailed it though…  every forum post you have is a row in that table… and the size of each row is going to be driven by how many characters in that post…  so only way to reduce is to prune older topics/posts…  though not sure you really need too… except eventually your mysql db will be too big to dump…  but replication is a better option anyways…

Yes – deleting a user via the WP Admin ‘Users’ panel also removes that user from the Simple:Press Members table. The SP sfmembers and WP users tables should have the same number of rows (although it is possible for some older SP forums to not be 100% the same). Not sure about multi-site either come to that!

We do have a plugin available that attempts to identify and optionally remove all ‘spam registrants’ – that is people who have registered on your site but never contributed any post or comment.

Also – when you say ‘housecleaning’ – are you actually also running a MySQL optimise on your tables? This is something I would recommend be done regularly – once a week say – and gets even more important as the tables get larger.

Mr Papa said
only way to reduce is to prune older topics/posts…

As I suspected, thank you for the clarification

Yellow Swordfish said
are you actually also running a MySQL optimise on your tables?

That, and them some. It had been a long time since doing any db maintenance (thanks for the reminder to do it more often). I was also able to delete more than a GB (yes gigabyte) of unnecessary data by running queries to delete old transients, commentmeta and post revisions.

Our sfmembers and users are indeed out of sync since not all members of the network are users of site_1 where we run the forums. But I would like to attempt to reduce the size of that table by deleting old sfmembers who have never posted and not visited since a certain date, or in over x days.

This appears like it will do the trick:

In our case, this will remove 1,750+ rows. Before I run it, however, I’m just wondering if any member related data (for these folks who have never posted) resides in other sf tables – auths, memberships, permissions, etc? Most of these other sf tables are small, but if I’m cleaning out members I do want to delete all of their related data. If I do need to delete rows from other tables, I could use some help editing this query.

(NOTE: I do realize this will not delete these sfmembers from wp_users. That’s OK, since our user table us only 850±kb, which I find odd since sfmembers is 30+MB.)

Aside from this, it sounds like our numbers are not unexpected nor uncontrollable for the site/forums of our size.

Thanks again for the feedback and help.

Correction: My actual query is as follows (I forgot to delete the * from my SELECT query):

DELETE 
FROM wp_1_sfmembers
WHERE posts = "0"
AND lastvisit < "2013-01-01 00:00:00"

Also, just curious… what’s that “REMOVE MARKER” business all about, in the post editor that does not appear and Mr Papa’s quote is not italicized…

Yes – there is data in other tables – as you assumed.

Again – our SP plugin to remove ‘spam registrations’ will remove all data associated with a user although this will include the WP user record as well.

We are having a little problem with quoting at the moment so am working on that. But I have no idea where this ‘remove marker’ bit comes from. Steve?

Yes – there is data in other tables …

Hmmm… any pointers as to what in which rows? I’d hate to remove 1800± sfmembers then have all their transient data hiding somewhere. I’ll take a loo at the spam reg plugin, but didn’t care to remove the users record since some may have sites on our network.

OK, I think I’m making some progress. After dissecting the spam reg plugin, I only found reference to what I believe to be six database tables. Please let me know if I’m on the right track, and be kind I’m learning as we go here…

To delete desired rows from sfmembers:

DELETE
FROM wp_1_sfmembers
WHERE posts = "0"
AND lastvisit < "2013-01-01 00:00:00"

Then, to clean up leftover user ids from sfmemberships:

DELETE FROM wp_1_sfmemberships WHERE user_id
NOT IN (
SELECT user_id
FROM wp_1_sfmembers
)

That’s where I get stuck. I see no db tables for these spdb_queries I found in the plugin:

• SFUSERS
• SFUSERMETA
• SFWPPOSTS
• SFWPCOMMENTS

A. Are my queries above correct for deleting sf_members and their related sf_membership data without deleting their wp_user table entries?

B. Where will I find these other tables/rows and what do I need to delete from them?

those are wp tables…  you are just looking at a define.. but they point to the standard wp users, usermeta, posts and comments tables…

I have no idea what that remove marker is.. never seen it before…  dont think its from us…

btw, you can clean out any transients in your options table on forum – toolbox – housekeeping…  should be happening auto periodically but cron can be suspect…

As to question A then yes.

It is probably pretty safe to assume that if the user has made no forum posts then they will not be active in other tables (like private messaging for example). But any if the forum tables with a user_id column can potentially have user related data. But it will not be much and will not be harmful if orphaned.

Good luck keeping the WP transient rows under control in the options table! I really dpn’t know how they came up with that particular total mess but mess it is!

Mr P. said
those are wp tables…

That’s what I thought, just needed to confirm we weren’t missing some tables!

Yellow S. said
It is probably pretty safe…

Thanks again for the feedback. I ran these queries, and was able to reduce the size of the sfmembers table, but only by about 8MB. Think I’ll quit while I’m ahead. but every little bit counts, eh?

Good luck keeping the WP transient rows under control in the options table!

I hear ya there! Our wp_1_options had grown to 60+ MB. I got it down to 1.5 MB using the following…

DELETE FROM `wp_1_options` WHERE `option_name` LIKE ('_transient_%');

Finally, should at some point we decide to clean out the wp_users I’ve now deleted from sfmembers, would this do the trick for me in bulk?

DELETE FROM wp_users WHERE user_id
NOT IN (
SELECT user_id
FROM wp_1_sfmembers
)

Thanks again! I really appreciate all the hand-holding through this.

That query should work OK against the users table. You would need to run the same thing against the usermeta table as well.

we do a periodic clean up of the transients just like you within simple press… but of course, we just clean up any SP transients… dont really want to take a chance mucking with core or other plugin transients that we may not understand…

maintenance can be a chore!

(When I do them I just delete the lot!  And good riddance. It is a horribly flawed sub-system…)

Yellow Swordfish said:

That query should work OK against the users table.

I wanted to review these users before deleting them. But when I run this query:

Select * FROM wp_users WHERE user_id NOT IN ( SELECT user_id FROM wp_1_sfmembers )

I get the following error:

#1054 – Unknown column ‘user_id’ in ‘IN/ALL/ANY subquery’

I have confirmed, however, that the user_id column does indeed exist in wp_1_sfmembers. Any thoughts?