时间戳

首页 > 解决方案 > Laravel 搜索查询优化

laravel - Laravel 搜索查询优化

问题描述

希望你做得很好！

我的数据库中有超过 4500 个businesses。每个都有business 很多 tags。所以我在导航栏中有我的主要搜索输入。当用户通过该搜索输入提交任何字符串时，我想string在他们的任何tags 或/和它的name字段中显示包含此字符串的所有企业。

示例：当string = ab时。它必须显示在其名称或/和字段businesses中包含string ab的前 20 个。tag name

结果：

  1. Name: Nur ab是Tags: 酒精饮料
  2. Name: Boirs Tags: 手机、手机配件、标签
  3. Name: BabilonTags :有线电视、移动网络运营商
  4. ……

生成并执行了这个 Eloquesnt/DB 查询

$businesses = Business::
            ->select('businesses.*')
            ->leftJoin('business_tag', 'businesses.id', '=', 'business_tag.business_id')
            ->leftJoin('tags', 'business_tag.tag_id', '=', 'tags.id')
            ->orWhere("tags.{$this->lang}_name", 'LIKE', "%$str%")
            ->orWhere('businesses.name', 'LIKE', "%$str%")
            
            ->where('businesses.status', true)
            ->groupBy('businesses.id')
            ->with(['tags'])
            ->withCount(['reviews as rating' => function($query){ 
                $query->select(DB::raw('round(avg(rating), 1)')); 
            }])
            ->withCount('reviews')
            ->with(['reviews' => function($query){
                $query->latest();
            }])
            ->with(['images' => function ($query)
                {
                    $query->where('avatar', true);
                }])
            ->paginate(20);

问题是执行需要21.05 秒。这是我的调试器显示的。这是花费大部分时间的两个查询：

6.24s

select count(*) as aggregate from `businesses` 
left join `business_tag` on `businesses`.`id` = `business_tag`.`business_id` 
left join `tags` on `business_tag`.`tag_id` = `tags`.`id` 
where (`tags`.`en_name` LIKE '%ab%' or `businesses`.`name` LIKE '%ab%') 
and `businesses`.`status` = 1 group by `businesses`.`id`

和14.78 秒

select `businesses`.*, 
     (select round(avg(rating), 1) from `reviews` 
      where `businesses`.`id` = `reviews`.`business_id` and `status` = 1) as `rating`,

     (select count(*) from `reviews` where `businesses`.`id` = `reviews`.`business_id` 
      and `status` = 1) as `reviews_count` from `businesses` 
left join `business_tag` on `businesses`.`id` = `business_tag`.`business_id` 
left join `tags` on `business_tag`.`tag_id` = `tags`.`id` 
where (`tags`.`en_name` LIKE '%ab%' or `businesses`.`name` LIKE '%ab%') 
and `businesses`.`status` = 1 group by `businesses`.`id` limit 20 offset 0

但是当我像这样评论那个标签部分时

$businesses = Business::
            ->select('businesses.*')
            // ->leftJoin('business_tag', 'businesses.id', '=', 'business_tag.business_id')
            // ->leftJoin('tags', 'business_tag.tag_id', '=', 'tags.id')
            // ->orWhere("tags.{$this->lang}_name", 'LIKE', "%$str%")
            ->orWhere('businesses.name', 'LIKE', "%$str%")

我需要40.47 毫秒来执行。如果我像这样评论名称部分

$businesses = Business::
            ->select('businesses.*')
            ->leftJoin('business_tag', 'businesses.id', '=', 'business_tag.business_id')
            ->leftJoin('tags', 'business_tag.tag_id', '=', 'tags.id')
            ->orWhere("tags.{$this->lang}_name", 'LIKE', "%$str%")
            // ->orWhere('businesses.name', 'LIKE', "%$str%")

耗时90.84ms

现在的问题是：有没有办法优化这个查询？
先感谢您！祝你今天过得愉快！

标签： laraveleloquentquery-optimizationquery-builder

解决方案

Try this below.See how much time it take time to execute.Aslo instead of lefjoin you can create belongsToMany relationship in Business Model for Tags

$businesses = Business::
        select('businesses.*')
        ->leftJoin('business_tag', 'businesses.id', '=', 'business_tag.business_id')
        ->leftJoin('tags', 'business_tag.tag_id', '=', 'tags.id')
        ->where(function ($q)use($str){
            $q->where('businesses.status', true);
            $q->orWhere("tags.{$this->lang}_name", 'LIKE', "%$str%");
            $q->orWhere('businesses.name', 'LIKE', "%$str%");
        })
        ->groupBy('businesses.id')
        ->withCount(['reviews as rating' => function($query){
            $query->select(DB::raw('round(avg(rating), 1)'));
        }])
        ->withCount('reviews')
        ->with(['reviews' => function($query){
            $query->latest();
        }])
        ->with(['images' => function ($query)
        {
            $query->where('avatar', true);
        }])
        ->paginate(20);

推荐阅读