Efficient Foreach loop querying database

Question

I am displaying images on my website with their details, however of late the site is very slow at loading.

The main thing is this foreach loop that loops through 100 times to display 100 posts in a grid. It takes 14 seconds to run

foreach($posts as $post) {

            $hashtags[] = $this->HashTagsModel->get_hashtags($post["id"]);
            $author[] = $this->UserModel->get_user_details($post["user_id"]);
            $comment_count[] = $this->CommentModel->get_comments_count($post["id"]);
            $is_favourited[] = $this->FavouriteModel->is_favourited($post["id"]);
            $is_reposted[] = $this->RepostModel->is_reposted($post["id"]);
            $vote_status[] = $this->vote_status($post["id"]);
            $comments[] = $this->count_comments($post["id"]);

        }

How can I do this differently to make it more efficient? This worked before our websites database became massive

Any help would be appreciated,

regards,

Almost Fired

Well if you already suspect that the growing database is the cause (you are probably right there), then you cannot solve this problem on php level. You have to take a closer look at the sql statements, the table and especially the index definitions inside the database! So for us to help you have to post your database structure and queries. — arkascha
– arkascha, Commented May 15, 2014 at 6:14
100 posts with 7(+) sub-queries should not take so much time. I suggest you to check the execution time on each of this 7 functions 1 by 1. I bet one of them causes most of the time issues. — d.raev
– d.raev, Commented May 15, 2014 at 8:33

viion · Accepted Answer · 2014-05-15 14:20:13Z

2

The efficient way to foreach loop query database is to not foreach query database. This is because you are allowing an unknown amount of queries to be fired off which will cause massive queueing. What if suddenly 5000 images get added, do you loop through them all? That query will take a very long time.

You have $post["id"] as your where variable I am assuming, so you could reduce this process significantly by doing a single query after formulating an array of post ids, something like this:

$postids = array();
foreach($posts as $post) {
    $postids[] = $post['id'];
}

// Selecting from 1 table
$query = 'SELECT * FROM hashtags WHERE id IN ('. implode(",", $postids) .')';

This would fetch all the information on hashtags where the id is is one of your postids. That is just 1 table, you would likely want to fetch multiple, without knowing your database structure I'm going to be generic, so something like:

// Selecting and joining data from multiple tables
$query = ' SELECT author.name, table.col FROM posts
LEFT JOIN author ON author.id = post.author_id
LEFT JOIN table ON table.id = post.table_id
WHERE posts.id IN IN ('. implode(",", $postids) .')';

It is a bit more difficult to be more accuracy. I think joining tables would provide you a better result, you can even join counts for votes/comments. If that is not possible, you could query all data related to your posts and then formulate it in PHP, then you know exactly how many queries you have. For example, change your models to accept an array instead of a single post ID and then change your "WHERE post_id = x" to "WHERE post_id IN (x)" then you can do something like:

$postids = array();
foreach($posts as $post) {
    $postids[] = $post['id'];
}

$hashtags = $this->HashTagsModel->get_hashtags($postids);
$author = $this->UserModel->get_user_details($postids);
$comment_count = $this->CommentModel->get_comments_count($postids);
$is_favourited = $this->FavouriteModel->is_favourited($postids);
$is_reposted = $this->RepostModel->is_reposted($postids);
$vote_status = $this->vote_status($postids);
$comments = $this->count_comments($postids);

This gets your queries outside of the loop, and you know there will only ever be 7 SQL queries, and not queries * posts. In PHP you would loop through the results of each array to assign each one back to posts based on its ID.

edited May 15, 2014 at 14:20

answered May 15, 2014 at 7:23

viion

3582 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Ellwood Over a year ago

Wow this is really helpful! I am going to do your last suggestion however I am having a few issues. For the author one I am finding it only returns one instance of the post that the author made. Eg Author 'B' made 3 posts, however it is just returning it once and stuffing up the order? Any suggestions on how to do this?

viion Over a year ago

Without knowing how your models work and what you are trying to accomplish it is difficult to get a clear picture. If you have 5 post ids, such as: [1, 3, 4, 4, 10], then when you pass that to get_user_details, it is going to find a result for each one but only where the post id is unique, so id 4 would only return once. Logically it wouldnt matter to much about the order, as you would loop through the results and append the data to something else. I am wondering why for a single post you need to know that the author of that single post had made 3 other posts?

Collectives™ on Stack Overflow

Efficient Foreach loop querying database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related