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.
Sep 28, 2018
7
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?
Level 73
1 like
Please or to participate in this conversation.