duangouyan3328 2016-09-09 16:57
浏览 78
已采纳

如何使用laravel加速Mysql查询

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).

  • 写回答

2条回答 默认 最新

  • dreamice2013 2016-09-09 17:52
    关注

    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');
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 ELGamal和paillier计算效率谁快?
  • ¥15 file converter 转换格式失败 报错 Error marking filters as finished,如何解决?
  • ¥15 ubuntu系统下挂载磁盘上执行./提示权限不够
  • ¥15 Arcgis相交分析无法绘制一个或多个图形
  • ¥15 关于#r语言#的问题:差异分析前数据准备,报错Error in data[, sampleName1] : subscript out of bounds请问怎么解决呀以下是全部代码:
  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码