4
\$\begingroup\$

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;
}

}

\$\endgroup\$
  • 3
    \$\begingroup\$ Does the slowness appear to come from the database queries, or from PHP after it fetches the data? If from the database, have you verified that the tables are properly indexed on any fields that you query frequently? \$\endgroup\$ – Phrancis Jun 16 '16 at 14:04
  • 1
    \$\begingroup\$ What I think is the query by limiting the post feed, is loaded normally. It seems that the script queries all database queries \$\endgroup\$ – Jefer Jun 16 '16 at 14:21
  • 1
    \$\begingroup\$ You have a } at the end of your code block, is it normal ? Have you paste all the code ? \$\endgroup\$ – Marc-Andre Jun 16 '16 at 14:33
  • \$\begingroup\$ It has been over a week and you haven't chosen an answer. Did none of our responses help you? \$\endgroup\$ – BeetleJuice Jun 28 '16 at 12:20
4
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
3
\$\begingroup\$

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;
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.