Migrating non-WordPress CMS to WordPress, lots of data to move — possible solutions?

I have a custom CMS, that we’re looking to port over into WordPress. (I can see you asking yourself:

Why not just stick with the custom CMS?

Well, it’s outdated, clunky, and getting to be too much of a hassle to maintain; there are also many features of WordPress that we would really like to take advantage of.

So here’s the issue — We have a main type of content (let’s call them Tours). Each Tour has about 30 pieces of metadata associated with it (all currently different columns on the Tours table). We currently have about 600,000 Tours.

Each Tour has several different types of images associated to it, from Interior Images, to Exterior, to Floor Plans, etc. Each Image has about 10 pieces of metadata associated with it (again, all currently columns on the Images table). We currently have about 7 million Images in our system.

You can likely see our problem — if we put all Tours and Images into the default wp_posts and wp_postmeta table, we’re going to end up with millions of rows in wp_posts, and far more than that in the wp_postmeta table. I’m not sure how much mySQL can handle as far as numbers of rows, but it seems likely that we will reach a slowdown point, especially when querying the wp_postmeta table.

My questions:

1. One possibility we’ve discussed is only holding “active” Tours in the wp_posts and archiving older Tours to a separate table. Is anyone aware of any plugins that do anything like this?

2. Another possibility is putting the Tours into the wp_posts table, and keeping the Images separate from that. One thing we would really like to use, though, is the WordPress image uploader. Is there anyway to override where the image data is saved?

3. Any other suggestions of how we might best implement this much data into WordPress?

I would suggest inserting your tours as posts (or custom posts) and using a custom reference table with the id matching the post id of the Tour post.

You could use WordPress’s built in meta box functions to be able to edit each Tour in the edit posts page.

You could update your reference table by hooking into the ‘save_post’ action.

There will be a considerable look-up speed improvement if you choose to create this reference table as MYISAM and use fixed column widths along with indexing searchable columns.

With that much data, you could use this option to not only tap in to much of the great WordPress built-in functionality, but ensure speedy look-ups on the extra data.