I am no MySQL expert, but I have handled so far to compromise together something which works. Regrettably, my latest bodged attempt leads to the server dying, so clearly I am doing something that's massively inefficient. Can anybody produce an indication regarding where the issue is and just how I would obtain the same results without getting the entire site lower each time?

$sqlbest = "SELECT  
        wp_postmeta.meta_value 
      , wp_posts.post_title 
      , wp_posts.ID
      , (TO_DAYS(CURDATE())- TO_DAYS(wp_posts.post_date))+1 AS days 
    FROM  `wp_postmeta` ,  `wp_posts` 
WHERE  `wp_postmeta`.`post_id` =  `wp_posts`.`ID` 
  AND  `wp_posts`.`post_date` >= DATE_SUB( CURDATE( ) , INTERVAL 1 WEEK) 
  AND  `wp_postmeta`.`meta_key` =  'views' 
  AND  `wp_posts`.`post_status` =  'publish' 
  AND wp_posts.ID != '".$currentPostID."'
GROUP BY  `wp_postmeta`.`post_id` 
ORDER BY (CAST(  `wp_postmeta`.`meta_value` AS UNSIGNED ) / days) DESC 
LIMIT 0 , 4";

$results = $wpdb->get_results($sqlbest);

It utilizes a publish sights count to calculate sights/day for posts released within the last, then orders them with that number, and grabs the very best 4.

I believe I observe that it's inefficient for the reason that it needs to calculate that sights/day each time for any couple of 1000 posts, but I'm not sure how to get it done much better.

Thanks ahead of time.

You can eliminate the necessity to call individuals date functions each time by either passing them statically in to the query out of your PHP server (which might not be synced together with your database) or rather write a saved procedure and save the outcomes of individuals date functions to variables which will then be utilized within the query.

SELECT  
  wp_postmeta.meta_value 
  , wp_posts.post_title 
  , wp_posts.ID
  , DATEDIFF(CURDATE(),wp_posts.post_date)+1 AS days <<--1: DATEDIFF
FROM  wp_postmeta
INNER JOIN wp_posts ON (wp_postmeta.post_id =  wp_posts.ID) <<--2: explicit join
WHERE wp_posts.post_date >= DATE_SUB( CURDATE( ) , INTERVAL 1 WEEK) 
  AND wp_postmeta.meta_key = 'views' 
  AND wp_posts.post_status = 'publish' 
  AND wp_posts.ID != '".$currentPostID."'
  AND wp_postmeta.meta_value > 1   <<-- 3: extra filter
/*GROUP BY wp_postmeta.post_id */  <<-- 4: group by not needed
ORDER BY (CAST( wp_postmeta.meta_value AS UNSIGNED ) / days) DESC 
LIMIT 0 , 4;

I have attempted to create a couple of changes.

  1. Changed the 2 calls to TO_DAYS with one call to DATEDIFF.
  2. Changed the ugly implicit where join by having an explicit inner join this doesn't do anything whatsoever, just makes things clearer. One factor it shows, if wp_postmeta.post_id is exclusive, then you don't need the audience by, since the inner join is only going to give one row per wp_postmeta.post_id.
  3. Added an additional filter to remove the posts having a low view count, this limits the quantity of rows MySQL needs to sort.
  4. Removed group by this really is only right if wp_postmeta.post_id is exclusive!