@lokedi have you try indexing the columns in the where clause?
Jul 6, 2021
2
Level 1
Optimize laravel query
Hi, I made this query to retrieve more data from a few tables .. but currently I tested with many pictures .. and it loads in 4 minutes I also tested with super many data of order 8k elements in the database and it loads super hard. Do you know a way to optimize your query? Could I use something from laravel to get rid of these joins or something?
$results = app(ProductFlatRepository::class)->scopeQuery(function($query) use($term, $categoryId, $params) {
$channel = request()->get('channel') ?: (core()->getCurrentChannelCode() ?: core()->getDefaultChannelCode());
$locale = request()->get('locale') ?: app()->getLocale();
$query = $query->distinct()
->addSelect('product_flat.*')
->addSelect('product_images.path as image')
->leftJoin('products', 'product_flat.product_id', '=', 'products.id')
->leftJoin('product_categories', 'products.id', '=', 'product_categories.product_id')
->leftJoin('product_images', 'products.id', '=', 'product_images.product_id')
->where('product_flat.status', 1)
->where('product_flat.visible_individually', 1)
->where('product_flat.channel', $channel)
->where('product_flat.locale', $locale)
->whereNotNull('product_flat.url_key');
So that's the query ... do you think I have a way to get off the lines and do something more optimized?
Please or to participate in this conversation.