Webapp that has one function that is slow.
I ask to suggest code 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 a function that takes a 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
</select>
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>
@endforeach
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');
}
I think you should re think how you made this application.
It makes no usable sense to have 30k options in a select, who is going to go through 30k options of the select checking in and out the options? Who would even read those options?
It makes no sense, is an application that is not possible to use. And why would you make an application that no one can use?
First you need to think of the problem, then on how to solve the problem, how your solution will actually help someone to solve the problem, instead of causing a more complicated problem. Throwing all the data out is not a solution for anything.
You probably should think of a AJAX/React/Vue.js or similar front end thing that allows the user to have an interaction with the data in terms that makes sense.
But, that takes time, so, you may at least solve it quickly by creating instead a page where you could filter workers based on name or something, then, and use pagination, each option having a checkbox next to it, indicating if is selected or not and allowing to select additionals, or instead of a checkbox a color or icon that indicate the current status and a button to add/remove it.
That way the user can search for the options it wants, navigate things, change things in a more suitable and doable way.
Note: also, make sure you have indexes in your database in every column that relates to other data, every column that you order, every column that you use to filter.
Your problem is not as much the query, as it is the massive amount of data that you are throwing in the HTML