Instagram-like news feed display

Question

I am writing an application that shows a news feed, much like Instagram. For this, I have a function in the model to select the feeds as shown in the code. For some reason, this runs quite slow and I am sure my code is the issue. Is there something I could do to optimise this.

public static function getPublicFeed($userId, $page=0, $limit=10, $color=NULL,            $lowerPriceLimit=NULL, $upperPriceLimit=NULL, $item_type_id=NULL, $gender=NULL){

try {

    $bindingsArray= array('user_id' => $userId, 'user_id_2' => $userId);

    $query="SELECT shop_posts.id AS post_id,
            's' as provider,
            shop_posts.shop_id as id,
            shops.name,
            shops.phone,
            shops.image,
            shop_posts.title,
            shop_posts.description,
            shop_posts.price,
            shop_posts.gender,
            shop_posts.images AS post_images,
            shop_posts.available_sizes,
            shop_posts.available_colors,
            (SELECT latitude FROM shops WHERE id=shop_posts.shop_id) AS latitude,
            (SELECT longitude FROM shops WHERE id=shop_posts.shop_id) AS longitude,
            (SELECT item_name FROM item_types WHERE id=shop_posts.item_type_id) AS type,
             (SELECT count(*) FROM likes WHERE post_id=shop_posts.id) AS likes_count,
            (SELECT count(*) FROM comments WHERE post_id=shop_posts.id) AS comments_count,
            (SELECT count(*) FROM likes WHERE post_id=shop_posts.id AND user_id= :user_id ) AS is_liked,
            CASE 
              WHEN DAY(TIMEDIFF(NOW(),shop_posts.created_at)) != 0 THEN CONCAT(DAY(TIMEDIFF(NOW(),shop_posts.created_at)) ,' d ago')
              WHEN HOUR(TIMEDIFF(NOW(),shop_posts.created_at)) != 0 THEN CONCAT(HOUR(TIMEDIFF(NOW(),shop_posts.created_at)) ,' h ago')
              WHEN MINUTE(TIMEDIFF(NOW(),shop_posts.created_at)) != 0 THEN CONCAT(MINUTE(TIMEDIFF(NOW(),shop_posts.created_at)) ,' m ago')
              ELSE
                 CONCAT(SECOND(TIMEDIFF(NOW(),shop_posts.created_at)) ,' s ago')
            END as time_since,
           (SELECT count(*) FROM follow_shops WHERE user_id= :user_id_2 AND shop_id=shop_posts.shop_id) AS is_following
             FROM shop_posts JOIN shops ON shop_posts.shop_id=shops.id WHERE shop_posts.sold=0";

    if (($color != null) && (!empty($color)) ) {
        $colorArray = json_decode ( $color );
        if (count ( $colorArray ))
            $query = $query . " AND WHERE shop_posts.color IN impode(',',:colorArray)";

        $bindingsArray['colorArray'] = $colorArray;

    }

    if (($lowerPriceLimit != null) && ($upperPriceLimit != null) && (!empty($lowerPriceLimit)) && (!empty($upperPriceLimit)) ){
        $query = $query . " AND WHERE shop_posts.price > :lower_limit AND shop_posts.price < :upper_limit";

        $bindingsArray['lower_limit'] = $lowerPriceLimit;
        $bindingsArray['upper_limit'] = $upperPriceLimit;
    }

    if (($item_type_id != null) && (!empty($item_type_id)) ){
        $query = $query . " AND WHERE shop_posts.item_type_id= :item_type";

        $bindingsArray['item_type'] = $item_type_id;
    }
    if (($gender != null) && (!empty($gender)) ){
        $query = $query . " AND WHERE shop_posts.gender= :gender";

        $bindingsArray['gender'] = $gender;
    }


        $query.= " ORDER BY shop_posts.id DESC LIMIT " . $page * $limit . "," . $limit;


    $feeds= DB::select( DB::raw($query), $bindingsArray);

    $path= url('/uploads/images/posts') . "/";

    foreach($feeds as $feed)
    {
        $feed->image=$path.$feed->image;
        $feed->post_images=unserialize($feed->post_images);
        $feed->available_colors=unserialize($feed->available_colors);
        $feed->available_sizes=unserialize($feed->available_sizes);

        $tempArray= array();

        foreach ($feed->post_images as $postImages) {
            array_push($tempArray, $path . $postImages);
        }
        $feed->post_images= $tempArray;

    }

    return $feeds;

    }
catch (\Illuminate\Database\QueryException $e) {

    return Response::json(array('success'=> 0,'msg'=> $e->getMessage()), 400); 
}
}

Answer 1

The biggest problem with your performance is that your query is MASSIVE. And I mean it.

Depending on the size of your database, you are going to see major performance hits from using the NOW() scalar function in your query. This is because it has to be called for every single row in the result, even if it isn't used. Creating a table-value function then using CROSS APPLY will greatly save you time in these 7 areas. Here's a great tutorial on using APPLY.

The next biggest thing, you have several nested statements inside. The best thing your going to be able to do is turn this into a stored procedure and call that from the PHP. Right now, you might be having to send and make multiple trips across the network to retrieve the requested data. This slows you down immensely. With a SP, it would hit the database and then execute the whole thing without leaving before returning one piece of data.

This article does a great job of explaining the Do's and Don'ts of SQL Queries. There are several parts of it that apply to your script.

Hope this helps you speed up your script! I know it's been a while!