Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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)

share|improve this question
5  
Instead of change the post_table why not use something like wordpress.org/extend/plugins/hyperdb to split the tables between databases/servers? –  Pontus Abrahamsson Mar 1 '13 at 9:57
1  
Could you elaborate what is precisely your concern (performance?) and how having extra tables resolves it? WP is known to handle very large quantities of posts (with sufficient hardware of course). –  Rarst Mar 1 '13 at 16:36
    
Thank You Pontus - HyperDB seems like it might be over my head at this point. I am looking into it though. –  user28216 Mar 1 '13 at 20:18
    
Rarst - I'm no DBA, but from researching forums, people seem to see a slow-down at around 50K post_table entries; and other than splitting the tables, I haven't seen a solid fix to it. The slow down seems to occur particularly, in the AdminPanel, and in Taxonomy/Category & Custom wp_query listing views. The site will be relying heavily on Tax/Cat/Custom query views, which is what concerns me most. I'm uncertain why WP did not split off a separate "uploads" table, so that the posts table would not become bloated so quickly. It seems like a legacy design problem that will need fixing soon. –  user28216 Mar 1 '13 at 20:32
    
Rarst - Also, forgot to mention in "Search" queries, which does a full-table %keyword% search, a large table could slow down dramatically. I can see the site having 5,000 posts within 2 years. With 100 photos per post, that is 500,000 post_table entries. So MySQL now has to go through 500K entries for the search, vs only 5K entries (had they split Uploads into a separate table). Once again, I'm no DBA, but that has to dramatically slow a search down. –  user28216 Mar 1 '13 at 20:45
show 2 more comments

5 Answers

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.

share|improve this answer
    
"Shift the images" was indeed my hope. However, there are 3 actions in the life of a file: 1. Upload Media. 2. Edit Media. 3 Delete Post (which deletes all media). I would need hooks in all 3 of those actions, and I can't find any hooks that help me to do all 3. –  user28216 Mar 9 '13 at 20:32
add comment

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.

share|improve this answer
    
There's dbDelta() for that task. –  kaiser Sep 23 '13 at 10:59
add comment

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/

share|improve this answer
add comment

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.

share|improve this answer
1  
The question is about a separate table. The plugin you recommend doesn’t do that. –  toscho Jun 25 '13 at 7:38
    
Actually the plugin stores it in the wp_postmeta table, which is separate from the wp_posts table :) –  Terry Kernan Jun 25 '13 at 10:12
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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