Support

Account

Home Forums Backend Issues (wp-admin) Database cleanup, feedback on function

Solving

Database cleanup, feedback on function

  • Hello all,

    I have a site that manages artworks and they have about 7500 records, each artwork post has 40 fields, which equals about 90 total post meta records per record.

    My postmeta table has 411,970… 124325 have empty meta, so with how ACF adds a field key, that is over 248650 wasted records, more than half the meta table.

    I do know tables can hold a lot of data, but with my custom queries using a lot of meta queries, I am getting some really slow results. Mainly when searching all posts and including ACF custom fields.

    I made a function that I *think* will clear out all blank meta records AND remove the related ACF key.

    Would anyone be able to glance over it to see if you notice any issues? I ran tests on my development server and it seems to be working well, but before I do it on a active site I wanted to get some feedback or refinements.

    
     //Update post content
    function anagram_clean_up_meta(){
    
    						    $args = array(
    						    	'posts_per_page' => -1,
    								//'post_type'      => 'artwork', //select only one post type
    								'post_status' 	 => 'any',
    								'meta_value'     => null,
    								//'post__in' 		=> array( 8019 )//testing with one single post
    						    );
    
    							  $my_query = new WP_Query($args);
    							    if( $my_query->have_posts() ) {
    								    //echo '<pre>';
    							      while ($my_query->have_posts()) : $my_query->the_post();
    								  	echo get_the_ID().'<br/>';
    										$myvals = get_post_meta(get_the_ID());
    
    										foreach($myvals as $key=>$val)
    										{
    											if(  $val[0] ==''  ){
    										    	//echo $key . ' : ' . $val[0] . '<br/>';
    												//$meta = get_post_meta(get_the_ID(),'_'.$key, true) . '<br/>';
    												//echo  $meta;
    												delete_post_meta(get_the_ID(), $key );
    												delete_post_meta(get_the_ID(), '_'.$key );
    
    										    }
    										}
    endwhile;
    //echo '</pre>';
    							    }
    }
    anagram_clean_up_meta();
    
    
  • Although I don’t think the number of records in the table should effect the speed of queries significantly, there have been reported issues with WP when doing queries with meta queries. It each post has a significant amount of meta values to get you can also reduce the number of queries using a statement you’re using above $myvals = get_post_meta(get_the_ID()); which will cause WP to get all values and cache them. Then you can use the function with specific meta_key values without causing a new db query.

    Looking at your code.

    Have you tried running any part of this without doing the deletes first?

    For example, does the query actually return any posts? I’m not sure you can run a query with a meta_value without also including a meta_key. I’d try doing a print_r on the posts to see if there are any.

    The next think I notice is that you don’t have a ‘global $post;’ statement. So this would only be able to run in a template file.

    The last thing is that with 7500 posts and trying to delete 100K+ meta values this will more than likely time out if loaded in a web page.

    You may be better off using $wpdb, but I’m not an expert on $wpdb.

    I would also make sure I’m deleting only ACF fields and not all fields with no content as there may be other plugins that depend on those empty records being there.

    So, I would get the meta data, then I’d make sure that there is a '_'.$key that has a value that starts with 'field_' before deleting it.

    I would also make sure I’m getting and deleting the right data by echoing stuff out to the screen before I actually did any deleting.

    Once I was sure it was going to work I’d probably set this up a s CRON task so that I wouldn’t need to worry about it timing out.

  • Thanks John, really helpful advice!

    I have echoed the posts with post meta on a single post and multiple to make sure it was working.

    I like the suggestion of checking to make sure the field has a related field key.

    I will look into the corn job, from my tests, it timed out when doing more than 500 posts. Would likely set the corn to 100 posts to be safe.

    I also would agree that the number of records is not the issue, but in this case, my shared hosting kept throttling my site and I tracked down the slow queries to pulling data from the postmeta table, likely I can clean up my functions a bit, but it all related to searching for posts by custom meta.

    Also I like to clean up stuff like 240,000 extra rows in a table that are not used =)

  • If you can do it with a cron task then you don’t need to worry about it timing out. You can set_time_limit(0);

    Like I said, there are some known issues with doing queries and joins or multiple inner joins from the post to the postmeta table. I’ve recently read about it but don’t recall where. There are plans to fix it, but that doesn’t solve you problem now. But what this means is that if it’s queries for searching posts by post meta, depending on how many meta_query values you’re supplying it may not help the problem.

    Also, it won’t stop the empty rows from building up in the database in the future. If this is a concern then you should create an acf/save_post action to remove empty fields when posts are published or updated in the future. http://www.advancedcustomfields.com/resources/acfsave_post/

    I’d really like to know if removing the empty values helps your situation, it it does I’ll likely build a plugin for myself that does this on all ACF fields.

Viewing 4 posts - 1 through 4 (of 4 total)

The topic ‘Database cleanup, feedback on function’ is closed to new replies.