Welcome to the Question2Answer Q&A. There's also a demo if you just want to try it out.
+4 votes
in Q2A Core by

Unnecessary data are accumulated in my site. As result, my database is becoming larger and larger.

My example:

  • Tables of deleted plugin
  • Hidden posts
  • Not used cache data
  • Old event data
  • Messages of deleted users
  • Search words which are not linked from any posts (?)
How about on your site?‚Äč
Which part do you mean by "Not used cache data"?

1 Answer

+2 votes

I will add some detail on the items and give my opinion on how to prevent/fix them, if possible, as not all users are aware of this or have any clue about how to fix them:

Tables of deleted plugin: I will first reference the convention post. New plugins sometimes generate new tables and new values in the ^options table. In order to clean this up you could:

  • Go through all the source code and find the settings and new tables used: Obviously, too time consuming and error prone!
  • Check with the developer if the plugin is following the convention I linked above: If it is, then it is a matter of performing the appropriate DELETE statement in the ^options table and the corresponding DROP TABLE commands in the unused tables
  • Ask the developer of the plugin to add an Uninstall button: I've done this myself in one of my plugins but most users don't think this actually adds value so I stopped doing so

Hidden posts: This one is a bit tricky. Although you can perform a DB operation in the admin/stats section that deletes hidden posts, if I remember correctly, it deletes all hidden posts that have no child. So sometimes this gets into a situation in which hidden posts can not be deleted. EG: if an answer has a comment and the answer is hidden it will not be deleted because it has an unhidden child. Maybe I'm wrong but this is how I remember it works

Not used cache data: Not sure what this refers to but maybe is something you can clean with the Refill of events streams in the admin/stats?

Old event data: The core itself does not save event data. So you might be referring to a plugin that stores those events, most likely, event logger plugin. In this case, if you want to keep that information for future reference probably you should, manually or by a CRON job (or a fake CRON, maybe?), perform the SELECT INTO statement to duplicate the data in a separate table and then remove the data from the original one. If you just care about the X latest records then the process can safely DELETE the records in the original table that match a time or a record count condition.

Messages of deleted users: I think this one refers to private messages and wall posts. This one is a bit more arguable as I think it is matter of getting into a product owner shoes. Are wall posts of deleted users garbage that should be deleted with the users themselves? Does this apply to private messages too? The way I see it I'm inclined to believe the answer to both questions is "Yes". Deletion in Q2A is really a DELETE... there is no logical deletion of information. This means data no longer exists and can not be recovered. Now, there must be a good reason to delete a user. I can only think about a spammer or a user that often brakes the site rules. In both cases it seems fair to also remove the private messages and wall posts. This could be changed in the core or be performed by a plugin that listens to the user delete event

Search words which are not linked from any posts: Those words can be removed by the Reindex content button in the admin/stats section

+1 Good suggestion. And, uninstallation feature of JAPB is good. There may be still a lot of garbage other than my example. I want to know it.