Social network queries for profiles, posts, and followers

Question

The code below is for a social networking system. The problem is that when there are many posts and many followers, the performance is slow. Is something wrong with the queries and the script below?

public function __construct($dbo = NULL)
{
    parent::__construct($dbo);
}

public function count()
{
    $count = 0;

    $stmt = $this->db->prepare("SELECT * FROM profile_followers WHERE follower = (:followerId) ORDER BY create_at DESC");
    $stmt->bindParam(':followerId', $this->requestFrom, PDO::PARAM_INT);

    if ($stmt->execute()) {

        while ($row = $stmt->fetch()) {

            $stmt2 = $this->db->prepare("SELECT count(*) FROM posts WHERE fromUserId = (:fromUserId) AND removeAt = 0 ORDER BY createAt DESC");
            $stmt2->bindParam(':fromUserId', $row['follow_to'], PDO::PARAM_INT);
            $stmt2->execute();

            $count = $count + $stmt2->fetchColumn();
        }
    }

    return $count;
}

public function getMaxId()
{
    $stmt = $this->db->prepare("SELECT MAX(id) FROM posts");
    $stmt->execute();

    return $number_of_rows = $stmt->fetchColumn();
}

public function get($itemId = 0)
{
    if ($itemId == 0) {

        $itemId = $this->getMaxId();
        $itemId++;
    }

    $feed = array("error" => false,
                  "error_code" => ERROR_SUCCESS,
                  "itemId" => $itemId,
                  "items" => array());

    $stmt = $this->db->prepare("SELECT * FROM profile_followers WHERE follower = (:followerId) ORDER BY create_at DESC");
    $stmt->bindParam(':followerId', $this->requestFrom, PDO::PARAM_INT);

    if ($stmt->execute()) {

        $items = array();

        while ($row = $stmt->fetch()) {

            $stmt2 = $this->db->prepare("SELECT id FROM posts WHERE fromUserId = (:fromUserId) AND id < (:itemId) AND removeAt = 0 ORDER BY id DESC");
            $stmt2->bindParam(':fromUserId', $row['follow_to'], PDO::PARAM_INT);
            $stmt2->bindParam(':itemId', $itemId, PDO::PARAM_INT);
            $stmt2->execute();

            while ($row2 = $stmt2->fetch())  {

                $items[] = array("id" => $row2['id'], "itemId" => $row2['id']);
            }
        }

        $stmt3 = $this->db->prepare("SELECT id FROM posts WHERE fromUserId = (:fromUserId) AND id < (:itemId) AND removeAt = 0 ORDER BY id DESC");
        $stmt3->bindParam(':fromUserId', $this->requestFrom, PDO::PARAM_INT);
        $stmt3->bindParam(':itemId', $itemId, PDO::PARAM_INT);
        $stmt3->execute();

        while ($row3 = $stmt3->fetch())  {

            $items[] = array("id" => $row3['id'], "itemId" => $row3['id']);
        }

        $currentItem = 0;
        $maxItem = 20;

        if (count($items) != 0) {

            arsort($items);

            foreach ($items as $key => $value) {

                if ($currentItem < $maxItem) {

                    $currentItem++;

                    $item = new post($this->db);
                    $item->setRequestFrom($this->requestFrom);

                    $itemInfo = $item->info($value['itemId']);

                    array_push($feed['items'], $itemInfo);

                    $feed['itemId'] = $itemInfo['id'];

                    unset($itemInfo);
                    unset($item);
                }
            }
        }
    }

    return $feed;
}

public function setRequestFrom($requestFrom)
{
    $this->requestFrom = $requestFrom;
}

public function getRequestFrom()
{
    return $this->requestFrom;
}

}

Answer 1

I'm not surprised your script is slow. You are being really wasteful with resources.

1. You should rewrite your code to access the DB as few times as possible. Reading from the DB is an expensive operation, because it requires PHP to connect to DB, send execution instructions, wait for results, parse results and make them available to your script. Notice for instance that in get(), your prepared $stmt2 and $stmt3 are identical. You're forcing your DB server to do the exact same work multiple times ($stmt2 is executed in a loop). Why not call $this->db->prepare once and just bind different parameters to it before executing? Better yet, can you compile all the fromUserIds you need and make the query once? Start to think in this manner to reduce DB queries.

2. When you access the DB on every iteration of a loop, you're really paying a heavy price. For instance in count(), you execute a query to retrieve all the data for a particular follower, then for each result you execute another query to count the rows in posts table that belong to this follower. Look around SQL resources/forums to figure out how to do this in one query that will return followerId, count. Trust me, it can be done (hint: look into SQL Join operations).

3. Only select the data you need. For instance, instead of SELECT * FROM profile_followers in count(), if all you care about is the userId so you can match it against the same ID in posts table, then use SELECT userId. The bigger your result set, the more memory your script will use.

4. NEVER sort if you don't need to. Sorting is really expensive. If you only care about the number of results (in count()), why are you sorting the results with ORDER BY x? Imagine if your boss asked you to sort all the words in this post alphabetically and report how many words there are. I bet you won't bother to sort the words; you'll just count them and report the total. Sorting is hard work.

Your intuition that your script is inefficient is right, so you have good instincts. Don't worry: once you're done optimizing you'll be amazed at the difference.

Answer 2

You've done several suboptimal things in just the count() method:

public function count()
{
    $count = 0;

    $stmt = $this->db->prepare("SELECT * FROM profile_followers WHERE follower = (:followerId) ORDER BY create_at DESC");
    $stmt->bindParam(':followerId', $this->requestFrom, PDO::PARAM_INT);

    if ($stmt->execute()) {

        while ($row = $stmt->fetch()) {

            $stmt2 = $this->db->prepare("SELECT count(*) FROM posts WHERE fromUserId = (:fromUserId) AND removeAt = 0 ORDER BY createAt DESC");
            $stmt2->bindParam(':fromUserId', $row['follow_to'], PDO::PARAM_INT);
            $stmt2->execute();

            $count = $count + $stmt2->fetchColumn();
        }
    }

    return $count;
}

• Avoid unnecessary ORDER BY: The order is irrelevant if all you want is a count.

• Avoid SELECT *: In $stmt, you are only interested in the follow_to column, so select just that column.

  SELECT follow_to FROM profile_followers WHERE follower = :followerId;
  

• Never execute queries in a loop: If the number of queries you execute varies according to the size of your data, then you will experience painful performance problems, as you have observed.

  The problem is that you aren't using SQL effectively. You should be able to formulate your query to get exactly the result you want.

Here is a way to write count() using just one query:

SELECT count(*)
    FROM posts
    WHERE
        removeAt = 0 AND
        fromUserId IN (
            SELECT follow_to
                FROM profile_followers
                WHERE follower = :followerId
        );

You should be able to apply similar principles to reduce the get() function to one query.

SELECT posts.id AS itemId
    FROM posts
        LEFT OUTER JOIN profile_followers
            ON posts.fromUserId = profile_followers.follow_to AND
               profile_followers.follower = :followerId
    WHERE
        posts.removeAt = 0 AND
        :followerId IN (posts.fromUserId, profile_followers.follower) AND
        (:itemId = 0 OR posts.id < :itemId) AND
    ORDER BY id DESC
    LIMIT 20;