Как оптимизировать данный SQL код?

Question

[Maqsat Batyrqul]

Как можно получить эти данные с одним запросом, сам смог только тремя запросами

$result_kazpost = DB::select('SELECT SUM(orders.delivery) AS kazpost
				  FROM orders
				  JOIN baskets ON  orders.basket_id=baskets.id
				  WHERE baskets.purchase_status = 3 AND orders.deleted_at IS NULL 
AND orders.delivery = "kazpost"
				  AND DATE(orders.created_at) >= :start AND DATE(orders.created_at) <= :finish',
				["start"=>$request->start,"finish"=>$request->finish]);

$result_courier = DB::select('SELECT SUM(orders.delivery) AS courier
				  FROM orders
				  JOIN baskets ON  orders.basket_id=baskets.id
				  WHERE baskets.purchase_status = 3 AND orders.deleted_at IS NULL 
 AND orders.delivery = "courier"
				  AND DATE(orders.created_at) >= :start AND DATE(orders.created_at) <= :finish',
				["start"=>$request->start,"finish"=>$request->finish]);

$result_pickup = DB::select('SELECT SUM(orders.delivery) AS pickup
				  FROM orders
				  JOIN baskets ON  orders.basket_id=baskets.id
				  WHERE baskets.purchase_status = 3 AND orders.deleted_at IS NULL 
 AND orders.delivery = "pickup"
				  AND DATE(orders.created_at) >= :start AND DATE(orders.created_at) <= :finish',
				["start"=>$request->start,"finish"=>$request->finish]);

Answer 1

[Melkij]

DATE(orders.created_at) >= :start

Никогда не использовать условия такого плана. Это прямой запрет использования индекса по created_at, довольно вероятно селективного для этих запросов.

SELECT orders.delivery, SUM(orders.delivery) AS summ
          FROM orders
          JOIN baskets ON  orders.basket_id=baskets.id
          WHERE baskets.purchase_status = 3 AND orders.deleted_at IS NULL 
              AND orders.created_at >= :start AND orders.created_at <= (:finish + interval 1 day)
          GROUP BY orders.delivery

И разобрать несколько строк ответа на приложении.

SELECT SUM(if(orders.delivery = "kazpost", orders.delivery,0)) AS kazpost,
SUM(if(orders.delivery = "courier", orders.delivery,0)) AS courier,
SUM(if(orders.delivery = "pickup", orders.delivery,0)) AS pickup
          FROM orders
          JOIN baskets ON  orders.basket_id=baskets.id
          WHERE baskets.purchase_status = 3 AND orders.deleted_at IS NULL 
              AND orders.created_at >= :start AND orders.created_at <= (:finish + interval 1 day)

В одну строку.

Затем смотреть explain

Comments

[Maqsat Batyrqul]

спасибо вам большое

Answer 2

[Алексей]

SELECT orders.delivery,SUM(orders.delivery) as SUMM
FROM orders
JOIN baskets ON orders.basket_id=baskets.id
WHERE baskets.purchase_status = 3 AND orders.deleted_at IS NULL
AND DATE(orders.created_at) >= :start AND DATE(orders.created_at) <= :finish

GROUP BY orders.delivery

Comments

[Maqsat Batyrqul]

суть запроса считать некоторые значения orders.delivery

[Алексей]

Maqsat Batyrqul: так добавьте эти некоторые значение в условие