Laravel DB query for REST API very slow

I'm doing my first steps with Laravel and try to write a music playlist. The database has 3 entities/models:

Play <-n-1-> Song <-n-1-> Artist

Tables creation:

        Schema::create('plays', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('song_id')->unsigned();
            $table->foreign('song_id')
                ->references('id')
                ->on('songs')
                ->onDelete('cascade');
            $table->dateTimeTz('date');
            $table->bigInteger('station_id')->unsigned();
            $table->foreign('station_id')
                ->references('id')
                ->on('stations');
            $table->timestamps();

            $table->unique(['song_id', 'date']);
        });

        Schema::create('songs', function (Blueprint $table) {
            $table->id();
            $table->bigInteger('artist_id')->unsigned();
            $table->foreign('artist_id')
                ->references('id')
                ->on('artists')
                ->onDelete('cascade');
            $table->string('title');
            $table->string('cover_url')->nullable();
            $table->smallInteger('cover_width')->nullable();
            $table->smallInteger('cover_height')->nullable();
            $table->string('asin')->nullable();
            $table->date('last_cover_check')->nullable();
            $table->timestamps();

            $table->unique(['artist_id', 'title']);
        });

        Schema::create('artists', function (Blueprint $table) {
            $table->id();
            $table->string('name')->unique();
            $table->timestamps();
        });

The database has about 856000 plays with 33000 songs and 15000 artists.

At first I tried to use the models directly with eager loading of plays->songs->artist.

return PlayResource::collection(Play::whereDate('date', $date)->get());

Using Insomnia this query on the REST API via Laravel takes 2.6s. This is very slow, I think.

If I understand Laravel correctly it does to much DB queries:

• One query for plays
• another one for songs
• But than for every play/song a separate query for its artist (?)

So I tried to create a single JOIN query to have only one DB query:

return DB::table('plays')
    ->leftJoin('songs', 'plays.song_id', '=', 'songs.id')
    ->leftJoin('artists', 'songs.artist_id', '=', 'artists.id')
    ->select('plays.*', 'songs.*', 'artists.*')
    ->whereDate('plays.date', $date)
    ->orderByDesc('plays.date')
    ->get();

This query is a little bit faster but still slow: 2.2s

The equivalent SQL query called directly on the DB is much faster:

SELECT *
FROM plays p
LEFT JOIN songs s ON p.song_id = s.id
LEFT JOIN artists a ON s.artist_id = a.id
WHERE DATE(p.date) = "2020-12-30"
ORDER BY p.date DESC;

-> 0.4s

Am I doing something wrong or is this typical overhead when using Laravel?

EDIT1:

OK, I've found DB::getQueryLog() (but that was not what I'm looking for). Now I know: Using three tables with chained eager loading causes only 3 DB queries. My first one is the slowest with nearly 400ms. The two queries only taking 1-2ms each.

I also found Lumen which should be faster than Laravel. So I tried it. My first query using models takes 1.4s now. Adding the JsonResources for output mappings it adds 0.3s -> 1.7s. All of this is still to slow in my opinion.

But I think this is the price I have to pay using a modern Framework. (My old playlist from 2006 doesn't use any frameworks).

EDIT2: The first query can be significantly enhanced by:

1. adding an index on date column
2. and querying without using functions (DATE(`date`) or ->whereDate('date', ...)) and instead using ->whereBetween('date', [$date, $date.' 23:59:59']) Now the first query takes only 5ms (from 400ms before). And I'm 1.3s for the whole Lumen query.

The SQL queries are taking only <10ms now. So Lumen overhead is about 99%. Really bad. I'll search for further tuning potential.

EDIT3 I did now create this exact same REST API using Rust(actix_web, diesel) and the same request with same DB takes 16ms. It's crazy how slow these PHP framework is. I will now focus on Rust and drop PHP/Laravel/Lumen for the backend.