DB Query inside a foreach loop is making my application slow

Question

The code bellow is used to show a table with 15 results in a page. For this, i'm using two different databases, one is a wordpress database, the other is a personal database i created.

The first Query is used to get the table values from the wordpress database, but it doesn't get the user name, since wordpress only stores the user ID in that table. The only place where i have the correct user name is on my second personal database.

To do this, i used foreach loop to replace the IDs for the user names. So, inside the loop, there's a new query used to get the user names.

My problem is that, every time a load the page, i'm running 16 DB queries at once, so it makes 16 database requests, and this is making my page slow.

public function index() {

    $posts = DB::connection('mysql2')
        ->table('wp_rocketsciencebrposts')
        ->select('ID', 'post_title', 'post_status', 'post_author', 'post_date')
        ->whereIn('post_status', ['publish', 'private'])
        ->where('post_type', 'post')
        ->orderBy('id', 'desc')
        ->paginate(15, ['*'], 'posts');

    $posts = compact('posts');

    foreach($posts['posts'] as &$value){

        //this DB Query is making my page slow, since it's inside this foreach loop, therefore, making 16 database requests
        $value->post_author = DB::connection('mysql')
            ->table('users')
            ->select('name')
            ->where('rsbwordpressid', $value->post_author)
            ->value('name');

    }

    return view('posts/posts', $posts);

}

I'm sure the solution is very simple, but i can't come out with a strategy on how to put the second DB Query outside the loop, avoiding all that unecessary database requests.

Pls, help.

1) Populate the table via AJAX, not source code; 2) Cache the result. — Mitya, Mar 06 '20 at 16:32
Sorry, but i'm a newbie and that would be too much for me. I'm not using AJAX anywhere else in my project. I think that maybe there is a solution by making another loop and storing values inside a array, using this to making only one DB request, but i don't really know how can i do this. — Neyelson Alves, Mar 06 '20 at 16:35
Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; for server workload tuning analysis to provide suggestions for making your design tolerable as it is. — Wilson Hauck, Mar 06 '20 at 23:18

score 1 · Answer 1 · answered Mar 06 '20 at 17:07

So you may encounter some oddities using both Laravel & Wordpress. I can give you some advice from a Laravel POV on how you could make this better.

Eloquent Models

If you haven't already, make two models (Post & User) and setup relationships between them.

User.php

class User extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'wp_rocketsciencebrposts';

    public function posts()
    {
        return $this->hasMany(Post::class, 'post_author', 'name');
    }
}

Post.php

class User extends Model
{
    /**
     * The table associated with the model.
     *
     * @var string
     */
    protected $table = 'users';

    public function user()
    {
        return $this->belongsTo(User::class, 'name', 'post_author');
    }
}

Controllers

Inside your controller you should now be able to do something like this

$post = Post::with('user')
            ->whereIn('post_status', ['publish', 'private'])
            ->where('post_type', 'post')
            ->orderBy('id', 'desc')
            ->paginate(15, ['*'], 'posts');

Now each of your Post objects in $posts should have a ->user realtionship loaded.

You can now go $post->user->name to retrieve the name of the author of the post

score 0 · Answer 2 · answered Mar 06 '20 at 19:20

0

You should to use one query with join:

$posts = DB::connection('mysql2')
    ->table('wp_rocketsciencebrposts')
    ->join('users', 'users.rsbwordpressid', '=', 'wp_rocketsciencebrposts.post_author')
    ->select('ID', 'post_title', 'post_status', 'post_author', 'post_date', 'users.name')
    ->whereIn('post_status', ['publish', 'private'])
    ->where('post_type', 'post')
    ->orderBy('id', 'desc')
    ->paginate(15, ['*'], 'posts');

answered Mar 06 '20 at 19:20

Slava Rozhnev

9,510
6
23
39

Hey, this solution doesn't work because, as i mentioned, i'm using two tables from different databases, so i'm getting a "table" not find error with your code. Although, i think that using join would be a solution for me. I posted another question in order to find the solution using the method you proposed. Could you spare few minutes and check it out? Thanks! -> https://stackoverflow.com/questions/60578741/how-to-use-join-in-bd-query-laravel-using-two-different-databases – Neyelson Alves Mar 07 '20 at 16:24

score -1 · Accepted Answer · answered Mar 09 '20 at 10:30

That's the answer i was expecting to see:

public function index() {

    $posts = DB::connection('mysql2')
    ->table('wp_rocketsciencebrposts')
    ->select('ID', 'post_title', 'post_status', 'post_author', 'post_date')
    ->whereIn('post_status', ['publish', 'private'])
    ->where('post_type', 'post')
    ->orderBy('id', 'desc')
    ->paginate(15, ['*'], 'posts');

    $user_ids = $posts->pluck('post_author')->toArray();

    $users = DB::connection('mysql')
    ->table('users')
    ->whereIn('rsbwordpressid', $user_ids)
    ->pluck('rsbwordpressid', 'name')
    ->toArray();

    $posts = compact('posts');

    $users = array_flip($users);

    foreach($posts['posts'] as $value) {

        $value->post_author = $users[$value->post_author];

    }

    return view('posts/posts', $posts)->with('mensagemSucesso', print_r($users) );

}

DB Query inside a foreach loop is making my application slow

3 Answers3