Instagram-like news feed display

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

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!

StackExchange Code Review Q#75045, answer score: 2