It takes a long time to loop over 1000 entries from my database in Laravel

Question

I am setting up an api end point that is responsible for getting movies from my database. I have a many to many relationship with genre. They are connected with a pivot table. I am trying to group all the movies by their genre before I send it over the response. Chrome is showing that it is taking 7+s TTFB (Time To First Byte). I need to know where the slow down is happening. I have also tested the endpoint with Postman and showing the same results. I don't know if I'm doing something wrong with the relationships, looping over the database, getting the data.

I am using Laradock to provide my Mysql, PHP, NGINX. I have tried to break out of the loop so that each genre group only can have 10 movies with in them. Doing this sped up my time from 20+s -> 7+s.

There are: 11 genres 1300 movies 3205 genre_movie

Example of the database movie:

 |id|name|...|
 |1 |mov1|...|
 |2 |mov2|...|
 |3 |mov3|...|
 |4 |mov4|...|

genre:

|id|genre   |...|
|1 |action  |...|
|2 |drama   |...|
|3 |thriller|...|
|4 |cartoon |...|

pivot: genre_movie

|movie_id|genre_id|
|1       |1       |
|1       |2       |
|2       |2       |
|3       |4       |

Here is my Relations Genre:

    public function movie(){
        return $this->belongsToMany('App\Movie');
    }

Movie:

    public function genre(){
        return $this->belongsToMany('App\Genre');
    }

Here is my migrations movies:

    public function up()
    {
        Schema::create('movies', function (Blueprint $table) {
            $table->bigIncrements('id')->unsigned();
            $table->string('title', 100);
            $table->text('synopsis');
            $table->integer('released_year');
            $table->string('imdb_url', 100);
            $table->string('s3_location', 100);
            $table->string('poster_location', 100);
            $table->boolean('isRestricted');
            $table->timestamps();
        });
    }

genres:

    public function up()
    {
        Schema::create('genres', function (Blueprint $table) {
            $table->bigIncrements('id')->unsigned();
            $table->string('genre');
            $table->longText('description');
        });
    }

genre_movie:

    public function up()
    {
        Schema::create('genre_movie', function (Blueprint $table) {
            $table->bigInteger('genre_id')->unsigned();
            $table->foreign('genre_id')->references('id')->on('genres');

            $table->bigInteger('movie_id')->unsigned();
            $table->foreign('movie_id')->references('id')->on('movies');
        });

Here is how I'm seeding my Data: Movie Factory

$factory->define(App\Movie::class, function (Faker $faker) {
    $faker->addProvider(new Image($faker));
    $faker->addProvider(new Base($faker));
    return [
        //
        'title' => $faker->name,
        'synopsis' => $faker->paragraph,
        'poster_location' => $faker->imageUrl($width=680, $height=680),
        'imdb_url' => 'https://www.imdb.com/title/tt5884052/',
        's3_location' => 'movie.mp4',
        'released_year' => $faker->numberBetween($min=1900, $max=1960),
        'isRestricted' => $faker->numberBetween($min=0, $max=1)
    ];
});

GenreTable Seeder

    public function run()
    {
        //

        $genres = ['action', 'adventure', 'comedy', 'crime','drama','fantasy','historical','horror','romance','science fiction','thriller'];
        $seeds = [];
        foreach($genres as $genre){
            array_push($seeds,[
                'genre' => $genre,
                'description' => Str::random(150)
            ]);

        }
        DB::table('genres')->insert($seeds);

    }

MovieTable Seeder

    public function run()
    {
        //
        $this->call([GenreSeeder::class]);
        factory(App\Movie::class, 1300)->create();

        $genres = App\Genre::all();

        App\Movie::all()->each(function ($movie) use ($genres) {
            $movie->genre()->attach(
                $genres->random(rand(1,4))->pluck('id')->toArray()
            );
        });

    }

Api Route

    Route::get('movies/filteredByGenre', 'MovieController@filteredByGenre');

MovieController@filteredByGenre

    public function filteredByGenre(Request $request){

        $movies = Movie::with('genre:genre')->get();
        $sizeofMovies = count($movies);
        $formatedMovie = [];

        $count = 0;
        for($x = 0; $x < $sizeofMovies; $x++){
            $sizeofGenre = count($movies[$x]->genre);
            for($y = 0; $y < $sizeofGenre; $y++){
                $genre = $movies[$x]->genre[$y];
                try{
                    if(isset($formatedMovie[$genre['genre']])){
                        if(sizeof($formatedMovie[$genre['genre']]) > 10){
                            break;
                        }
                        $formatedMovie[$genre['genre']][] = $movies[$x];
                    }else{
                        $formatedMovie[$genre['genre']][] = $movies[$x];
                    }
                } catch(ErrorException $e) {
                    $formatedMovie[$genre['genre']][] = $movies[$x];
                }

            }
        }
        $response = ['success' => true, 'data' => $formatedMovie ];
        return response()->json($response, 201);
    }

How I'm receiving the data in the front-end

    componentDidMount() {
        var url = '/api/movies/filteredByGenre';
        axios
            .get(url)
            .then(response => {
                return response.data;
            })
            .then(json => {
                console.log(json);
                this.setState({ frontPageMovies: json.data });
            });
    }

Commands to start my sever and seed the code

docker-compose up -d nginx mysql phpmyadmin workspace
php artisan db:seed --class=MoviesTableSeeder

I expect movies sorted by their genre and the time it takes is no more than 1-3s to retrieve the data.

Answer 1

The issues is how i retrieved the data from the database. The way i was using before was taking too long to retrieve the data. I switched the query to:

        $movies = DB::table('genre_movie')
        ->select('movies.*')
        ->addSelect('genres.genre')
        ->join('movies','genre_movie.movie_id','=','movies.id')
        ->join('genres','genre_movie.genre_id','=','genres.id')
        ->get();

I am too new to understand why. I think it is how i set up my database, model, or relationship; I'm not sure. Now my page loads in under 1 second.

If somebody can comment on why this is happening that would be helpful. I know that eloquent can handle this amount of data very easily.