I'm counting post views on a custom field inside wp_postmeta, the update happens each time a user interacts with the post. I was reading at this response Will a large postmeta table slow a site down? specifically:

"You should not write to the DB on front end requests" is the eleventh commandment. Your performance will deteriorate due to the writes locking down the table much more than the impact the size of the table will have.

You should probably write such data in files instead of DB, or at least use a different table.

So the idea is to accumulate these views on a new table and update the wp_postmeta with a WP-Cron function every hour or so. My question is: would this be bad for performance? Should I do it differently? or is it OK? I guess it will be about 500-1000 rows each run. Code:

global $wpdb;

$results = $wpdb->get_results( "SELECT * FROM total_views", OBJECT );

foreach($results as $key=>$value){

    //for new posts without meta
    $old_count = 0;
    if ( metadata_exists( 'post', $value->post_id, 'post_views' ) ) {
        $old_count = get_post_meta( $value->post_id, 'post_views', true );
    new_count = $value->post_views;
    $total_count = $old_count + $new_count;
    update_post_meta( $value->post_id, 'post_views', $total_count );
//done. empty table
$wpdb->query('TRUNCATE TABLE total_views');

I should add I'm interested on keeping wp_postmeta updated because other site functionality depends on it. Else I would just stick to the new table.

  • this strategy will not work regardless of the performance due to race conditions. The data you collect will be highly inaccurate as a result because of the time difference between reading and writing the data, and that it is not an atomic operation, coupled with post meta caches. Performance is not the only reason this isn't done, and storing this kind of data has special requirements that post meta etc don't meet. It's better instead to read that data into post meta from a seperate source of truth, and update it semi-regularly via a cron job. Your new table will have the same race conditions
    – Tom J Nowell
    Jan 9 at 16:37
  • that's also ignoring that searching for posts by their post meta values gets very expensive and slow as the size of the meta table increases
    – Tom J Nowell
    Jan 9 at 16:38
  • @TomJNowell Some more info: This site already uses a dedicated ElasticPress (Elasticsearch server) to handle retrieving posts everywhere (home, archives, search with filters). It skips WP database entirely. As for your 1st comment: The new table will have post ID as the primary key which i hope will make it faster than wp_postmeta. Then i guess i could keep the grand totals in there permanently and sync to postmeta with a cron, but my question remains, wouldn't that take a good while? since we are dealing with all posts now not only the ones with changes in visits. Jan 9 at 18:58
  • Could you not update your custom table on page load, but then in the background run an AJAX function that triggers after the page is done loading, that copies the data over to post_meta. That way you aren't running these large queries. (I'm with @TomJNowell that this isn't the way to do it, but since you insist.) You could also re-write the other functions that depend on that post_meta to use the custom table instead. Yeah, it sucks to have to go an refactor all that code, but if we're talking scalability, future proofing and optimizing... Jan 10 at 1:50
  • That's pretty much what i have now. But update_post_meta is a slow query. Jan 10 at 2:12


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.