Problem load time server with query bagisto mysql 8

LilD

Hi, I have about 8000 products on the website and the search is super hard and etc .. and from what I saw it's from the product query
How can I optimize it?

$qb = $query->distinct()
                 ->select('product_flat.*')
                ->addSelect('product_images.path as image')
                ->join('product_flat as variants', 'product_flat.id', '=', DB::raw('COALESCE(' . DB::getTablePrefix() . 'variants.parent_id, ' . DB::getTablePrefix() . 'variants.id)'))
                ->leftJoin('product_categories', 'product_categories.product_id', '=', 'product_flat.product_id')
                ->leftJoin('product_attribute_values', 'product_attribute_values.product_id', '=', 'variants.product_id')
                ->leftJoin('product_images', 'product_flat.product_id', '=', 'product_images.product_id')
                ->where('product_flat.channel', $channel)
                ->where('product_flat.locale', $locale)
                ->whereNotNull('product_flat.url_key');

devansh-webkul

This is an issue because of the product_attribute_values. Just try to fetch from the product_flats table column.

Kindly recheck the attribute_values