Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Lokedi's avatar

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?

0 likes
2 replies
Tray2's avatar

First of all remove the distinct since it might force the table to be full scanned. Then add the proper indexes as @neilstee suggested.

What you can do is dump your query to sql and then run an explain plan on the resulting sql and it will give you hints about what to change.

1 like

Please or to participate in this conversation.