Social network queries for profiles, posts, and followers

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 bindParam(':fromUserId', $row['follow_to'], PDO::PARAM_INT);
$stmt2->bindParam(':itemId', $itemId, PDO::PARAM_INT);
$stmt2->execute();

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

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

-
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.

-
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).

-
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.

-
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.

StackExchange Code Review Q#132189, answer score: 4