I'm building an "Ads" Website based on Laravel as an API where it contains a dynamic-advanced search.

Ads can have multiple attributes and the user can have advanced search, but it leads to so many sub-queries if user checked so many attributes to filter by and the query become slow, Any advice about better query than this one.

select
 *
from
  `ads`
where
  `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      `attribute_id` = 32
      and `value` in ('odio', 'quos', 'dignissimos', 'dolorem', 'qui')
  )
  and `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      `attribute_id` = 171
      and `value` in ('itaque', 'non', 'dolor', 'laborum')
  )
  and `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      `attribute_id` = 111
      and `value` in ('quia', 'non', 'nam', 'molestias')
  )
  and `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      `attribute_id` = 144
      and `value` in ('delectus', 'nam', 'exercitationem', 'sit')
  )
  and `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      `attribute_id` = 160
      and `value` in ('repellat', 'fugit', 'quaerat', 'vero')
  )
  and `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      `attribute_id` = 176
      and `value` in ('mollitia', 'voluptates', 'maxime', 'culpa')
  )
  and `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      `attribute_id` = 177
      and `value` in ('necessitatibus', 'id')
  )

and here's the code for the search, PS. I'm using a mpyw/eloquent-has-by-non-dependent-subquery instead of using whereHas because its even slower....

->when(!empty($search->attrs), function (Builder $query) use ($search) {
            foreach ($search->attrs as $key => $value) {
                if (!is_null($value)) {
                    $query->hasByNonDependentSubquery('adAttributes', function (Builder $q) use ($value, $key, $search) {
                        $q->where('attribute_id', $key)
                            ->when(is_array($value), fn($q) => $q->whereIn('value', $value))
                            ->when(!is_array($value), fn($q) => $q->where('value', $value));
                    });
                }
            }
            return $query;
        });

CodePudding user response：

Is there a reason not to just join the table and use where()?

$query = Ads::select('ads.*')->join('ads_attributes', 'ads.id', 'ads_attributes.ad_id');

if (!empty($search->attrs) {
    foreach ($search->attrs as $k=>$v) {
        $q->orWhere(function (Builder $q) (use $k, $v) {
            $q->where('ads_attributes.id', $k)
                ->when(
                    is_array($v),
                    fn ($q) => $q->whereIn('value', $v),
                    fn ($q) => $q->where('value', $v),
                );
        });
    }
}

return $query;

CodePudding user response：

The Entity-Attribute-Value schema pattern is notoriously verbose and inefficient. But it is flexible.

It may help to add this "composite" index (that is also "covering") to the table ad_attributes:

INDEX(attribute_id, value, ad_attributes)

CodePudding user response：

This may be absolutely useless and perform really badly but I vaguely remember using something along these lines when battling an EAV performance issue many years ago. I do not have a suitable test dataset to test this against so I may be making a complete fool of myself by suggesting this.

select
 *
from
  `ads`
where
  `ads`.`id` in (
    select
      `ad_attributes`.`ad_id`
    from
      `ad_attributes`
    where
      (`attribute_id` = 32  and `value` in ('odio', 'quos', 'dignissimos', 'dolorem', 'qui')) OR
      (`attribute_id` = 171 and `value` in ('itaque', 'non', 'dolor', 'laborum')) OR
      (`attribute_id` = 111 and `value` in ('quia', 'non', 'nam', 'molestias')) OR
      (`attribute_id` = 144 and `value` in ('delectus', 'nam', 'exercitationem', 'sit')) OR
      (`attribute_id` = 160 and `value` in ('repellat', 'fugit', 'quaerat', 'vero')) OR
      (`attribute_id` = 176 and `value` in ('mollitia', 'voluptates', 'maxime', 'culpa')) OR
      (`attribute_id` = 177 and `value` in ('necessitatibus', 'id'))
    group by `ad_attributes`.`ad_id`
    having count(`attribute_id`) = 7
  )