0

We need to remove large amounts of data (members, entries, etc) from the DB. What is the safest way to go about doing this? We're hoping we could run a delete query of sorts? Or that there is an extension that would help - but we've tried EE Support and searching around, with no real solution.

We have about 154807 members to remove; 1019974 entries to remove (for the entire channel); and 2143560 entries to remove from another (for the entire channel as well).

Unfortunately, we’ve been unable to remove these via the admin, it just freezes up and gives a time out error or memory limit error. Additionally, even trying to remove 500 at a time just for the channel entries (3163534 total entries) would mean having to do that over 6,000 times to get them all cleared out…

Essentially, this is a site that we had a cart system on for years, and need to remove all of that data, as they have migrated over to Magento for the cart, but are keeping the corporate site in EE.

UPDATE:

The current EE version is 5.4.1 The cart system we were using was CartThrob We also used the User module by EE Harbor to manage the members. Both CartThrob and User have been uninstalled.

Improve this question
1
  • 1
    Oof. There are only two ways to go off the top of my head. One would be a complex SQL deletion query per record type, but even then, you'd need to run batches. If you're sure this data can disappear with out consequence, I would consider creating a template (that is locked down to requests from localhost only) that has PHP enabled, and uses the channel or member model to delete a small batch at a time; then write a PHP script hit by a cron job that runs over night every few minutes. Just a thought.
    – jrothafer
    Commented Mar 13, 2021 at 4:49

1 Answer 1

Reset to default
1

We ended up running similar queries found here Quick and safe way to delete all entries with a given status? and here Quickly delete 1000s of members

We were actually able to remove millions of rows of data using these queries, and so far there hasn't been any issue. We've removed members and channel entries. We went from 15 million rows of data down to about 7 in our first attempt, and have been able to remove 4 other custom channels since using these queries.

Here are the queries we ran:

Delete Channel Entries

DELETE exp_channel_titles, exp_channel_data
FROM exp_channel_titles
INNER JOIN exp_channel_data
ON exp_channel_data.entry_id = exp_channel_titles.entry_id
WHERE exp_channel_titles.channel_id = XXX

Delete Members - skipping references to YYY, just in case*

DELETE FROM `exp_members` WHERE `username` NOT LIKE '%YYY%' and `group_id` = XXX

*We didn't want to remove any test members we may have created, matching XXX domain, in this group.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.