phpmyadmin Question posted in
The official documentation can be found here.

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

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('AppMovie');
    }

Movie:

    public function genre(){
        return $this->belongsToMany('AppGenre');
    }

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(AppMovie::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(AppMovie::class, 1300)->create();

        $genres = AppGenre::all();

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    If none of your tables are indexed, I’d start there. You can often get a big performance boost just by adding indexes and/or primary keys on your primary columns (The IDs in this case). You can think of them a bit like the index of a book … sure you could scan every single page but its a lot faster if you have a map of where everything is.

    Schema::create('movies', function (Blueprint $table) {
    $table->bigIncrements('id')->primary();
    ... or ...
    $table->index(['id']);
});

    I’d also suggest installing or enabling the Laravel Debug Bar from https://github.com/barryvdh/laravel-debugbar. This can show you whats happening in your application behind the scenes, including each and every database query that is made and how long those took to complete. Sort of like TTFB in Dev Tools but for the stuff Chrome can’t see.

    One final option I use a lot, is to have Laravel generate my database query but not execute it …

    $movies = Movie::with('genre:genre')->toSql();

    … and then executing it directly in my database (phpMyAdmin or your preferred console) If it’s fast there, I know its my php code. Otherwise I know I need to look at other DB optimizations.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search