Is there a way to archive old entries

Question

We have a large site with 100k+ entries in exp_channel_data The admin section straggles when trying to edit entries and change or add channel fields. It takes almost 80 seconds to add or alter a field and more than 10 seconds to list or edit an entry. Is there a way to archive old entries to a different channel (add-on maybe) or something similar? How do we deal with such large sites? You can find more about this here http://ellislab.com/forums/viewthread/238313/ Is there anything we can do about this? - or this is it? EE has its limits.

Answer 1

First, just note that it's safe to assume that adding more memory / faster servers for both your EE/Apache and MySQL will make things work better. 100k+ entries is enough to require more horsepower than a smaller site, but that generally can be addressed through reasonable upgrades.

That said, I was just working on a site with 75k+ entries--and we were definitely seeing performance issues around adding fields (especially because there were hundreds of fields already, with a lot of data in most of them). EE sticks all of your entry data ("custom fields") in one MySQL table, and every field change and entry change has to compete for lock access (and new fields = new MySQL columns which lock the whole table while it's being restructured).

And, just to add: this is a kind-of tricky situation where there's no one solution that matches every site's content and archiving needs.

So, you have some options, depending on what you mean by "archived."

What worked for me:

For the site I was just working on with 75k "archivable" entries, we simply made a usable backup of the site and DB, and then deleted the 75k entries and corresponding channel fields from the live site. These entries already were not visible on the live site, but they do need to be stored indefinitely for business reasons.

Now, we are able to bring up this backup copy of the site on a development machine if we need to access those entries. And the live site is much happier.

Other options:

If you want to make the Edit list faster when you're using filter by channel, you can move the old entries to a different channel(s). You need to create a new channel that has the exact same field, category and status groups as your current channel. Then, you should be able to go into an entry, and select between the current and archive channel in the Channel drop down.

If this works, then you can use MySQL to mass update change the channel_id for the entries that you want to move to the archive channel. The channel_id field is used in these tables in EE MySQL:

• exp_channel_data
• exp_channel_entries_autosave
• exp_channel_form_settings
• exp_channel_member_groups
• exp_channel_titles
• exp_channels
• exp_comments
• exp_entry_versioning
• exp_layout_publish

At a minimum, you'll need to update the channel_id for entries in exp_channel_titles and exp_channel_data. You'll need to update it in other tables if you're using the relevant featurs--e.g., if your entries have comments, you'll need to update in exp_comments, too. Also, some add-ons use channel_id, like Low Search stores the channel_id in exp_low_search_collections.

But, generally, you can handle the new channel configuration via the EE control panel, and then do a mass update via MySQL of the entry_id <-> channel_id relationships.

Or:

You could split your site into two EE installs, one with current entries, and one with the archived entries. These could live on two sets of Apache / DB servers. You could have these live at separate domains (e.g., example.com and archive.example.com), or use a reverse proxy to have one domain access both sites.

This would be easy to create from a MySQL backup. If your site continues to grow such that you eventually have to figure out how to move entries from the current to the archived copies of the site, that will be a little harder to deal with. But, again, developing queries in MySQL for selecting the relevant data, and then exporting it, will work.