Slow query in laravel

I have a laravel application but a query needs a lot of time to be executed.

I tryied to build a raw query but with no success

The query is this one:

$utente = Utente::with('coll')->with('collaboratori')
            ->where('id', '<>', '0')
            ->whereHas('coll', function ($query) use($id_utente, $att_dummy){
                $query->where('collaboratori_id', 'like', $id_utente);})
            ->orderBy('created_at')
            ->get();

I tryed to look the sql query with queryLog and there are different query that laravel executed:


select * from `utenti_nuovo` where `id` <> 0 and exists (select * from `collaboratori_utenti` where `utenti_nuovo`.`id` = `collaboratori_utenti`.`id_utente` 
and `collaboratori_id` like 100008) order by `created_at` asc

select * from `collaboratori_utenti` where `collaboratori_utenti`.`id_utente` in (718, 834, 844, 848, 875, 890, 894, 895, 897, 898)

select * from `collaboratori` where `collaboratori`.`id` in (12,13,16)

select `collaboratori`.*, `collaboratori_utenti`.`id_utente` as `pivot_id_utente`, `collaboratori_utenti`.`collaboratori_id` as `pivot_collaboratori_id`, 
`collaboratori_utenti`.`attivita` as `pivot_attivita`, `collaboratori_utenti`.`created_at` as `pivot_created_at`, `collaboratori_utenti`.`updated_at` as `pivot_updated_at` 
from `collaboratori` inner join `collaboratori_utenti` on `collaboratori`.`id` = `collaboratori_utenti`.`collaboratori_id` 
where `collaboratori_utenti`.`id_utente` in (718, 834, 844, 848, 875, 890, 894, 895, 897, 898)

The first of those query is the slow one that causes a long time to wait.

Is there a way to change the EXIST with a faster query?

2 answers

  • answered 2019-09-15 13:48 Jordan Lipana

    Only select fields you need. This will reduce the loading time.

  • answered 2019-09-15 17:49 Itzhak Avraham

    In order to optimize the query time, you can use laravel cursors. Instead of end your query with ->get(), just use ->cursor(), like this:

    $utente = Utente::with('coll')->with('collaboratori')
        ->where('id', '<>', '0')
        ->whereHas('coll', function ($query) use($id_utente, $att_dummy){
            $query->where('collaboratori_id', 'like', $id_utente);})
        ->orderBy('created_at')
        ->cursor();
    

    When working with big databases, it's better to use cursor. Here is a comparison of using get, chunk and cursor, from this answer:

    10,000 records:

    +-------------+-----------+------------+
    |             | Time(sec) | Memory(MB) |
    +-------------+-----------+------------+
    | get()       |      0.17 |         22 |
    | chunk(100)  |      0.38 |         10 |
    | chunk(1000) |      0.17 |         12 |
    | cursor()    |      0.16 |         14 |
    +-------------+-----------+------------+
    

    100,000 records:

    +--------------+------------+------------+
    |              | Time(sec)  | Memory(MB) |
    +--------------+------------+------------+
    | get()        |        0.8 |     132    |
    | chunk(100)   |       19.9 |      10    |
    | chunk(1000)  |        2.3 |      12    |
    | chunk(10000) |        1.1 |      34    |
    | cursor()     |        0.5 |      45    |
    +--------------+------------+------------+