Slow query in laravel

Question

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

I tried 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 also tried to look over the sql query with the 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?

Using like will avoid mysql using indexes. Also, do you have a index on your colaboratori_id? Buona sera! — Elias Soares, Sep 15 '19 at 13:56

score 1 · Answer 1 · answered Sep 15 '19 at 13:48

1

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

answered Sep 15 '19 at 13:48

Jordan Lipana

437
2
4
18

wont affect query speed that much. – N69S Sep 15 '19 at 13:53

score 1 · Answer 2 · answered Sep 15 '19 at 17:49

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    |
+--------------+------------+------------+

Slow query in laravel

2 Answers2