Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

deansatch's avatar

Very very slow query...how to speed up

I have this:


        $subQuery = function($q)use($start, $end){
                       $q->whereHas('classdates', function($q)use( $start, $end){
                            $q->whereBetween('datetime',[ $start, $end]);
                        );
                    };

        $subQuery2 = function($q)use($start, $end){
                        $q->whereHas('classes.classdates', function($q)use( $start, $end){
                            $q->whereBetween('datetime',[ $start, $end]);
                        });
                    };
        
        $studios = \App\Studio::
        select('name')->
        withCount(['classes' => $subQuery])
        ->withCount(['bookings' => $subQuery2])
        ->get();

It takes about 20 seconds to run just for a one month date range and I need it to run for full years or even more.

Is there a way I could speed this up?

0 likes
12 replies
kobear's avatar
kobear
8 years ago
Best Answer
Level 4

What database platform is it?

Some general steps that I use to troubleshoot database performance issues (on MySQL):

  • In artisan tinker chain your select statement into ->toSql() like so:
 $studios = \App\Studio::
        select('name')->
        withCount(['classes' => $subQuery])
        ->withCount(['bookings' => $subQuery2])
        ->get()->toSql();

This will output the raw SQL that is sent to the DB server from Eloquent.

  • Run that raw SQL statement directly against the mySQL database, but prepend EXPLAIN. This will give you an idea of the indexing that is used for the query.

I am betting that you are missing some kind of index in the database, probably on the datetime field.

3 likes
deansatch's avatar

@kobear Thanks...I couldn't get toSql() to work but I added an index for classes_id and superfast now

2 likes
kobear's avatar

@deansatch My bad. I got the chaining incorrect for toSql().

toSql() replaces the get() function in the chain, and only returns the SQL query without running it.

So you code would be

 $studios = \App\Studio::
        select('name')->
        withCount(['classes' => $subQuery])
        ->withCount(['bookings' => $subQuery2])
        ->toSql();
1 like
Cronix's avatar

You can also use Laravel Debugbar, which is immensely useful. It will show you the queries run on each page (along with the explain) and much more. It just runs behind the scenes and creates a bar at the bottom of the webpage, much like browser dev tools do.

https://github.com/barryvdh/laravel-debugbar

gstoa's avatar

I also use Laravel Debugbar and usually find that the problem is never with Laravel, but my choice (or lack thereof) of table indexes and partitions.

2 likes
kobear's avatar

@gstoa Laravel Debugbar is particularly useful because it lists the individual SQL query response times. This is extremely valuable when you have a page that has many different DB queries, and therefore you can focus on the exact queries that are taking longer than optimal.

murph133's avatar

Make sure you have indexes on your tables.

$table->index('user_id'); // Example migration code

Add the indexes and test again to see if there's a performance difference. My load time dropped by over 50%, when I added indexes to the related tables that were being used for the withCount.

1 like
moses_ndeda_KE's avatar

In 2020 I am using Laravel Telescope to profile queries instead of Laravel debugbar.

I have never used debugbar though.

Please or to participate in this conversation.