Inefficient SQL

5 Views

I’m no MySQL expert, but I’ve managed until now to hack together something that works. Unfortunately, my latest bodged attempt results in the server dying, so obviously I’m doing something that is massively inefficient. Can anyone give me a hint as to where the problem is and how I might get the same results without bringing the whole site down everytime? 

$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 uses a post views count to calculate views/day for posts published in the last, then orders them by that number, and grabs the top 4.

Read More

I think I see that it’s inefficient in that it has to calculate that views/day everytime for a few thousand posts, but I don’t know how to do it any better.

Thanks in advance.

Post Views: 5

Related posts

Leave a Reply

2 comments

  1. You could eliminate the need to call those date functions every time by either passing them statically into the query from your PHP server (which may not be synced with your database) or you can instead write a stored procedure and save the results of those date functions to variables that will then be used in the query.

  2. 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’ve tried to make a few changes.

    1. Replaced the two calls to TO_DAYS with one call to DATEDIFF.
    2. Replaced the ugly implicit where join with an explicit inner join this does not do anything, just makes things clearer. One thing it shows, if wp_postmeta.post_id is unique, then you do not need the group by, because the inner join will only give one row per wp_postmeta.post_id.
    3. Added an extra filter to filter out the posts with a low view count, this limits the amount of rows MySQL has to sort.
    4. Eliminated group by this is only right if wp_postmeta.post_id is unique!