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

dust's avatar
Level 9

Query optimization, ORDER BY slows my query

I have a query:

SELECT P.* FROM `products` AS P
JOIN `category_product` AS CP ON P.id = CP.product_id
JOIN `categories` AS C ON CP.category_id = C.id
WHERE C.id = 26
ORDER BY P.`created_at` DESC
LIMIT 4

Which I wrote in Laravel like that:

$products = Product::whereHas('categories', function ($query) use ($categoryId) {
                $query->where('categories.id', '=', $categoryId);
            })
            ->orderBy('products.created_at', 'desc')
            ->take(4)
            ->get();

The problem is that above query takes more than 5 seconds to execute.

I found that laravel translate my query to:

SELECT * FROM `products` 
WHERE EXISTS (
    SELECT * FROM `categories` INNER JOIN `category_product` 
        ON `categories`.`id` = `category_product`.`category_id` 
    WHERE `products`.`id` = `category_product`.`product_id` 
        AND `categories`.`id` = '26' ) 
ORDER BY `products`.`created_at` desc 
limit 4

I found also that if I remove ORDER BY row in second query ( and in laravel ), the time is OK. I mean it is few ms.

What can I do to decrease execution time? And why ORDER BY makes that problem?

0 likes
7 replies
Tray2's avatar
Tray2
Best Answer
Level 73

You are probably doing a full table scan which is not so good. I would suggest you add some indexes to your tables to make the scan faster.

1 like
dust's avatar
Level 9

@Tray2 should I set index to products.created_at in this case ?

Tray2's avatar

That is my suggestion yes.

1 like
dust's avatar
Level 9

@Tray2 WOW!!! 0.2288 seconds after I set index on products.created_at !!! Thank you very much!

dust's avatar
Level 9

But the original query is still much faster: 0.0037 seconds, may be I should rewrite my query with joins.

Cronix's avatar

Also add an index to category_product.product_id if it isn't already. Most things used in a where or order by should be indexed.

1 like

Please or to participate in this conversation.