Guanapado Pado Guanapado Pado - 10 months ago 63
MySQL Question

How can I Speed up Mysql query using laravel

Webapp that has one function that is slow.

I ask to suggest code how to speed up this function from 5-6 seconds to 1-2 seconds.

I have database with tables:

table: Workers id, first_name, last_name

table: Stores id, name

table: worker_store id, worker_id, store_id

The idea is that workers are connected to a single or multiple stores via worker_store table.

In Laravel app i have function that takes single store and shows that workers are connected to this particular store. This must be displayed as “select” field with connected workers marked as “selected” and all other workers as not selected. My Laravel blade code look like this

<select multiple name="workers[]" id="workers" class="form-control"> @foreach (App\Worker::orderBy('last_name')->get() as $worker) <option value="{{$worker->id}}" **@if ($object->hasWorker($worker->id)) selected @endif**>{{$worker->getName()}}</option> @endforeach


Database is Mysql and worker table has 30 000 records. It is very slow while laravel makes 30 000 separate queries to the database to check every worker if he is linked to a particular store (bold part of code above).

Please suggest a solution that doesn't need to query 30 000 times database and make generation of full in no more than 1-2 seconds (current code takes about 5-6 seconds).


This would be a lot to put in the view but here's the basic concept of what I think you are looking for...

// Grab all the workers so that we can loop through them.
$workers = App\Worker::all();

// Grab a list of the worker id's for the store we are currently searching for.
$storeWorkers = App\Store::with('workers')->find(1)->workers->lists('id');

// Use in_array to determine if the worker is currently working in the store we are looking at.
@foreach ($workers as $worker)
    <option value="{{ $worker->id }}" {{ in_array($worker->id, $storeWorkers) ? 'selected' : '' }}>{{ $worker->name }}</option>

This should net you a total of 3 queries. 1 to grab all the workers and 2 to grab the workers by store.

It would seem your models are not setup correctly. In your Store model, add the function...

public function workers()
    return $this->belongsToMany(Worker::class, 'worker_store');