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.

Answer 3

I suppose if you really do want to store the info in a different table, you could do, but the problem is managing that out of the box, adding such details as post id is easy as that is part of the global post object, but the image url (for a file that has just been uploaded) is not.

the problem is, to get the media into a table of it's own via the built in wordpress ui is that it would have to be uploaded using the media uploader which creates rows in the posts table as 'attachment' types, which defeats the original aim.

to remove rows from other tables when a post is deleted, you could do something like this:

add_action('admin_init', 'codex_init');
function codex_init() {
if (current_user_can('delete_posts')):
    // todo: remove records when posts are removed
    add_action('delete_post', 'post_sync', 10);
endif;
}

function post_sync($pid) {
    global $wpdb;
    // no need to check, just delete and watch our for number of records deleted
    return $wpdb->query($wpdb->prepare('DELETE FROM custom_table_name WHERE post_id = %d', $pid));
 }

You'll be able to get more information about the hooks here: http://codex.wordpress.org/Plugin_API/Action_Reference/

Answer 4

Could you try using a plugin? I've used Types from wp-types.com and it works a treat for images, it works by storing all the info as meta data, it allows you to manage everything from the UI too:

http://wp-types.com/home/types-manage-post-types-taxonomy-and-custom-fields/

as an added bonus, all the images are then displayed on the main UI screen as opposed to hidden behind the add media button, which sometimes confuses users I've dealt with. It will create all the different thumbnail sizes too. by keeping all the file data as post meta data, it means that when images are removed, the meta gets removed also, keeping the db tidy.

Answer 5

You mentioned that you don't want to use separate gallery , but that is the proper method to get it done. select a gallery a plugin which provide you the facility to attach it with a post either by shortcode or by a custom field, in this way you will have full control on both the posts and the galleries. You also can use Advanced custom field plugin ( repeater field enabled ) to make gallery within the custom field panel of posts.