Query optimization

Hello, hope you are doing good!

I have more than 4500 businesses in my database. Each business hasMany tags. So I have my main search input in navbar. When user submits any string via that search input I want to display all businesses that contain this string in any of their tags or/and in it's name field.

Example: When string = ab. It must display first 20 businesses that contains string ab in any of their tag name or/and name field.

Result:

Name: Nurabis Tags: Alcoholic Beverages
Name: Boirs Tags: Mobile Phones, Mobile Phone Accessories, Tablets
Name: Babilon Tags: Cable TV, Mobile Network Operators
......

This Eloquesnt/DB query was generated and executed

$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);

The problem is that it took 21.05s to execute. This is what my debugger shows. These are two queries that took most of the time:

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`

and 14.78s

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

But when I comment that tag part like so

$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%")

I takes 40.47ms to execute. And if I comment name part instead like so

$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%")

It takes 90.84ms

Now the question is: Is there any way to optimize this query? Thank you in advance! Have a nice day!

WebParaguay

4 years ago

Level 4

You can try excluding the tag name filter from your query and filter by tag name on the frontend app.

SilenceBringer

4 years ago

Level 55

@abdulaziz some suggestions

How many tags you have in your system? If it's about 100-200 - it make sence to store it in cache, search by tag name in cache first and then use ids in main query as

Business::
            ->select('businesses.*')
            ->leftJoin('business_tag', 'businesses.id', '=', 'business_tag.business_id')
            ->whereIn("business_tag.tag_id", $tagsIds)
            ->orWhere('businesses.name', 'LIKE', "%$str%")

(-1 joined table, search by id instead of tag name)

the second one is your aggregation queries. You select reviews count and rating for every business. By the way. later you load all reviews too. So, you can easily exclude this part from your query\

            ->withCount(['reviews as rating' => function($query){ 
                $query->select(DB::raw('round(avg(rating), 1)')); 
            }])
            ->withCount('reviews')

and later access both values from loaded relations as

@foreach ($businesses as $business)
    <div>Rating: {{ $business->reviews->avg('rating') }}</div>
    <div>Count: {{ $business->reviews->count() }}</div>
@endforeach

(- group by businesses.id in result query, do not join reviews table twice in main query)

By the way, the idea to load all reviews looks wrong for me. what if you'll have about 1000 reviews for every business? Load 20000 reviews every time?

The approach I can suggest here is to add reviews_count and rating fields to business table and update it every time new review added to the business

Be sure all ids fields are indexes
By default latest sorts by created_at field, which is not an index. I always recommend to use id instead, because it's key and will works faster

            ->with(['reviews' => function($query){
                $query->latest('id');
            }])

If your database will grows, I can suggest to use Elasticsearch with laravel Scout. It's extremely fast and optimized for text search. By the way, it will require some additional server configuration (you'll need to install it and configure properly). But result will suprise you

Please or to participate in this conversation.