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.
- Changed the 2 calls to
TO_DAYSwith one call to
- Changed the ugly implicit where join by having an explicit
inner jointhis doesn't do anything whatsoever, just makes things clearer. One factor it shows, if
wp_postmeta.post_idis exclusive, then you don't need the audience by, since the inner join is only going to give one row per
- Added an additional filter to remove the posts having a low view count, this limits the quantity of rows MySQL needs to sort.
group bythis really is only right if