Use a separate custom table (not posts) to handle file upload data

Question

The Problem: Website that has over 100 photos per post. Could possibly swell posts-table beyond 100K entries within a few years.

The Dilemma: Would much rather stick to default WP UI for handling images/files with the built-in "Add Media" Uploader. I'd like to avoid using a separate File Gallery implementation if possible.

The Question: Has anyone found a way to divert the saving of data away from the posts_table and to your own custom table to store the file-data? (without having to hack core files)

Answer 1

Perhaps there's a manageable way to shift the images from being store as attachments (aka posts) to being stored in an array that's kept at the parent post level. I think it's going to depend on how much you need to manipulate the images, delete them, etc.

Shifting to another table might help but it would seem to me you might be robbing Peter to pay Paul. Sure bigger tables hurt but if those rows are somewhere else and you still need a join, etc. how much is going to be saved? I think you might have to get a bit more outside the box to crack this nut.

Answer 2

You can manually create a new table in your phpMyAdmin on server (it's obviously your site that you will not move frequently to a new server) or, of course, you can hook table creation with theme installation or similar. Wordpress use the object/class $wpdb for achieving any query within Wordpress database. There you can see how to execute any query within WP database. So, I would use something like this:

<?php
global $wpdb; // Object must be globalized.

$wpdb->query("
CREATE TABLE IF NOT EXISTS ".$wpdb->prefix."new_table (
    id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    post_id bigint(20) NOT NULL,
    image_url varchar(250) NOT NULL,
    PRIMARY KEY (id),
    KEY post_id (post_id)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ");
?>

You can put it in a function and hook it to theme_init for instance or something similar. Change fields by your preferences. Another way is using dbDelta() function, which has some specific rules and because of that I don't see the right reason of using it (maybe there is some and I'm wrong) - you can find all about it here (I can't put more permalinks because of rep, sorry :D)

After creating the table, you can manipulate it any way you want - you can hook registering that attachments into new table. You can also hook some function that will prevent putting attachments into the posts-table. Because of the small number of columns (like postmeta table, for instance) I guess it's lightweight and therefore there have to be some performance improvement in manipulating with that images.