mercredi 3 février 2016

laravel5.1 optimize performance by reducing querys (MySQL)

While inspecting some pages of my project I saw the "overview" of support tickets page produces much SQL querys. Let me explain what this page does. It shows all the support tickets (and some details for tickets) opened by a user. It uses paginate to show them in a friendly way. How to reduce my amount of querys for better performance. My code:

table design:

tickets: |id|supp_id|title|user_id|...

ticket_replies: |id|ticket_id|user_id|text|created_at

files: |id|ticket_replie_id|name

user model:

public function tickets()
{
    return $this->hasMany('App\ticket');
}

ticket model:

public function ticket_replie() 
{
    return $this->hasMany('App\ticket_replie', 'ticket_id', 'id');
}

public function supporter()
{
    return $this->hasOne('App\User', 'id', 'supp_id');
}

ticket_replies model:

 public function user() 
{
        return $this->hasOne('App\User', 'id', 'user_id')->select(array('id', 'username'));
}

my controller

$tickets = Auth::user()
       ->tickets()
       ->with("supporter")
       ->orderBy('status', 'desc')
       ->paginate(5);


    return view('protected.ticketsList', [ 
        'tickets' => $tickets,
        ]);

current view:

<div class="container">
    @if(!$tickets->count() > 0)
    Noch keine Supporttickets erstellt.
    @endif

    @foreach ($tickets as $ticket)
        <br>
        ID: {{$ticket->id}}
        title: {{ $ticket->title}}<br>
        status: {{ returnStatus($ticket->status) }}<br>

        @if (!$ticket->supporter)
            supporter:-<br>
        @else
            supporter  {{ $ticket->supporter->username }}<br>
        @endif

        created at: {{ $ticket->created_at }}<br>

        Details: {{ url('support/ticket/'.$ticket->id) }}<br>

    @endforeach
</div>

{!! $tickets->render() !!}

It produces soo much querys:

amount of querys

enter image description here



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire