Bloated postmeta database

[cookieandkate]

Hi there, I am so thankful for this plugin, thank you! I'm trying to clean up my wp_postmeta database, and it has somewhere around 400,000 lines from STCR. That's a lot!

Is it possible to run a SQL query to remove all such lines that incurred before, or between, a set of dates? I don't think it's necessary to keep every commenter as a subscriber forever.

An example of a meta_key is _stcr@_email@address.com with a meta_value that includes the date like 2017-01-23 12:48:37|R)

Hope this makes sense, and I would really appreciate any solutions you might have to suggest. Thank you very much!

Kate

[BobaWebDev]

Hi @cookieandkate

Thanks, happy to hear you like it.

Will have to run some tests to make sure things don't break.

There's an update coming tomorrow, we might be able to include cleanup functionality in there so you can remove subscriptions older than a specific period of time.

We'll either include that functionality or I'll let you know here about the SQL query.

[cookieandkate]

Amazing, thank you so much!

On Tue, Jan 21, 2020 at 11:42 AM Slobodan Kustrimovic < notifications@github.com> wrote:

Hi @cookieandkate https://github.com/cookieandkate

Thanks, happy to hear you like it.

Will have to run some tests to make sure things don't break.

There's an update coming tomorrow, we might be able to include cleanup functionality in there so you can remove subscriptions older than a specific period of time.

We'll either include that functionality or I'll let you know here about the SQL query.

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub https://github.com/stcr/subscribe-to-comments-reloaded/issues/554?email_source=notifications&email_token=AA7N7WWNSYSYUNMBCKYTVL3Q64XZBA5CNFSM4KI2NCHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJQTQ2Y#issuecomment-576796779, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA7N7WVDAII3NWACB6FW3F3Q64XZBANCNFSM4KI2NCHA .

[BobaWebDev]

Hi @cookieandkate

Just wanted to let you know there's a delay, high fever for the past few days, hoping it'll be over by Monday and I'll be back at work.

[atomGit]

high fever for the past few days

vitamin C - try 3+ grams (3000 mg) /hr. (yes, hour) or more - should probably add some D3 also

[BobaWebDev]

Thanks @atomGit

Was drinking a lot of lemon/orange juice, enough for a whole year :)

I think it's finally over. 5 hours with normal temp. Hopefully it remains like that and tomorrow I'm back to work.

[BobaWebDev]

Hi @cookieandkate

Here's the SQL query:

DELETE FROM wp_postmeta WHERE meta_key LIKE '\_stcr@\_%' AND STR_TO_DATE(meta_value, '%Y-%m-%d %H:%i:%s') <= DATE_SUB(NOW(), INTERVAL 60 DAY)

Just replace the 60 at the end to the amount of days you want. You can also go with months, just replace DAY with MONTH

DELETE FROM wp_postmeta WHERE meta_key LIKE '\_stcr@\_%' AND STR_TO_DATE(meta_value, '%Y-%m-%d %H:%i:%s') <= DATE_SUB(NOW(), INTERVAL 3 MONTH)

And of course make sure to do a database backup first, just in case.

You can use the following query to return instead of delete, so you can see which rows would be affected:

SELECT * FROM wp_postmeta WHERE meta_key LIKE '\_stcr@\_%' AND STR_TO_DATE(meta_value, '%Y-%m-%d %H:%i:%s') <= DATE_SUB(NOW(), INTERVAL 3 MONTH)

[atomGit]

hi @BobaWebDev - i don't know what the best practices are, but i wonder if it's a good idea to add StCR to wp_postmeta? personally i prefer when plugins create their own table so that everything can be deleted when uninstalling them

[BobaWebDev]

Hi @atomGit

It was made like that before we acquired the plugin, a bit problematic to make bigger changes to the system at this point. If we go ahead with changing the system we'll probably rewrite the whole plugin, but that takes quite a lot of time and money of course.

Will talk to the owner. Perhaps monetizing the plugin somehow which will allow for more time to be spent on it.

[atomGit]

thanks for the reply - if you do decide to monetize the plug, i hope you'll do so in an ethical way (no data/mail address harvesting, etc.)

thanks!

[BobaWebDev]

Oh, no worries there. If it does get monetized it will be something like a paid version that adds more features.

Extremely unlikely that any features will be removed from the free version, the only thing would be to add additional features which are a paid add-on.