How can I improve the following query's performance while the server is loaded? I've created a combo index and removed the index from the table because it's not picking it up, but is there another way to do it? , Normally I run the below query it takes 198.24 ms once loaded on the server then it takes 24-30 sec approx.
SELECT
`u`.`id` AS `user_id`,
`orders`.`plan_id`,
`u`.`username`,
`allotments`.`status_updated_by_admin`,
`allotments`.`driver_ats_date`,
`allotments`.`admin_bts_date`,
`allotments`.`driver_bts_date`,
`pal_area` AS `route_name`,
count(distinct orders.order_id) AS `total_order`,
count(distinct orders.shipment_id) AS `total_address`,
count(
distinct (
case when orders.status != 'orderinprocess' then orders.order_id end
)
) AS `total_marked_order`,
count(
distinct (
case when orders.status = 'delivered' then orders.order_id end
)
) AS `total_deliverd`,
count(
distinct (
case when orders.status = 'cancel' then orders.order_id end
)
) AS `total_cancel`,
count(
distinct (
case when orders.status = 'reschedule' then orders.order_id end
)
) AS `total_reschedule`,
count(
distinct (
case when orders.status = 'orderinprocess' then orders.order_id end
)
) AS `total_process`,
count(
distinct (
case when orders.status != 'orderinprocess' then orders.shipment_id end
)
) AS `total_marked_address`,
`cart`.`out_of_stock`,
`routes`.`id` AS `route_id`,
`allotments`.`id` AS `allotment_id`,
`allotments`.`admin_cash_return` AS `cash_status`,
`pr`.`status` AS `route_check_status`,
(
CASE WHEN orders.source in(
'field', 'nonfield', 'non-field',
'o2o', 'b2bapp', 'field1', 'panel',
'e-bazaar', 'b2c-field', 'b2c-field1',
'b2c-non-field', 'b2c-b2bapp', 'b2b-inbound',
'outbound-b2b', 'inbound-b2b', 'b2c-outbound-b2b',
'b2c-inbound-b2b', 'b2c-o2o'
) THEN 1 ELSE 0 END
) AS `is_b2b`,
(
CASE WHEN routes.route_type = 'rvp' THEN 1 ELSE 0 END
) AS `is_rvp`,
`allotments`.`delivery_boy_id`,
`allotments`.`field_exc`,
`allotments`.`inventory_update_status`,
IF(rra.id IS NOT NULL, 1, 0) AS `is_rvp_route`,
`rra`.`allotment_bts_date` AS `rvp_allotment_bts_date`
FROM
`orders`
INNER JOIN `routes` ON routes.name = orders.pal_area
INNER JOIN `plan_routes` `pr` ON pr.route_id = routes.id
and pr.plan_id = orders.plan_id
INNER JOIN `warehouse_planid` ON warehouse_planid.plan_id = orders.plan_id
LEFT JOIN `allotment_route_details` `ard` ON ard.route_id = routes.id
and date(ard.created_date) = orders.delivery_date
and ard.plan_id = orders.plan_id
LEFT JOIN `allotments` ON allotments.id = ard.route_allocation_id
and orders.plan_id = allotments.plan_id
and allotments.allotment_date = orders.delivery_date
LEFT JOIN `rvp_route_allotments` `rra` ON rra.allotment_id = allotments.id
LEFT JOIN `user` `u` ON u.id = allotments.user_id
LEFT JOIN `cart_order_outofstock` `cart` ON cart.order_id = orders.order_id
WHERE
(
orders.delivery_date= '2022-12-27'
)
AND (
`warehouse_planid`.`warehouse_id` = '141'
)
AND (`orders`.`plan_id` = 'U238490')
GROUP BY
`pal_area`,
`plan_id`,
`is_rvp`,
`is_b2b`,
`allotments`.`id`
explain plan;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: warehouse_planid
type: const
possible_keys: wp_planid,warehouse_id,warehouse_planid_plan_id
key: wp_planid
key_len: 603
ref: const
rows: 1
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: orders
type: ref
possible_keys: delivery_date,pal_area,orders_plan_id,idx_plan_order_id
key: orders_plan_id
key_len: 603
ref: const
rows: 5569
Extra: Using index condition; Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: routes
type: eq_ref
possible_keys: PRIMARY,name_2,name
key: name_2
key_len: 767
ref: dealshare_delivery.orders.pal_area
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: ard
type: ref
possible_keys: route_id,plan_id
key: route_id
key_len: 5
ref: dealshare_delivery.routes.id
rows: 3
Extra: Using where
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: allotments
type: eq_ref
possible_keys: PRIMARY,allotment_date,allotments_plan_id
key: PRIMARY
key_len: 4
ref: dealshare_delivery.ard.route_allocation_id
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: rra
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 30
Extra: Using where; Using join buffer (flat, BNL join)
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: u
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: dealshare_delivery.allotments.user_id
rows: 1
Extra: Using where
*************************** 8. row ***************************
id: 1
select_type: SIMPLE
table: cart
type: ref
possible_keys: order_id_fk1
key: order_id_fk1
key_len: 4
ref: dealshare_delivery.orders.order_id
rows: 1
Extra: Using where
*************************** 9. row ***************************
id: 1
select_type: SIMPLE
table: pr
type: ref
possible_keys: route_id
key: route_id
key_len: 5
ref: dealshare_delivery.routes.id
rows: 3
Extra: Using where
Table structure;
Table: orders
Create Table: CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`transaction_type` varchar(255) NOT NULL,
`sub_type` varchar(255) NOT NULL,
`document_type` varchar(255) NOT NULL,
`doc_number` varchar(255) NOT NULL,
`doc_date` varchar(255) NOT NULL,
`warehouse` varchar(255) NOT NULL,
`deal_no` varchar(255) NOT NULL,
`product_name` varchar(255) NOT NULL,
`item_description` varchar(255) NOT NULL,
`hsn_code` varchar(255) NOT NULL,
`item_quantity` varchar(255) NOT NULL,
`weight` varchar(255) DEFAULT NULL,
`item_unit` varchar(255) NOT NULL,
`cgst_rate` varchar(255) NOT NULL,
`cgst_amount` varchar(255) NOT NULL,
`sgst_rate` varchar(255) NOT NULL,
`sgst_amount` varchar(255) NOT NULL,
`igst_rate` varchar(255) NOT NULL,
`igst_amount` varchar(255) NOT NULL,
`cess_rate` varchar(255) NOT NULL,
`cess_amount` varchar(255) NOT NULL,
`transaction_value` varchar(255) NOT NULL,
`order_id` varchar(255) NOT NULL,
`source` varchar(255) NOT NULL,
`verified_date` varchar(255) NOT NULL,
`dispatch_date` varchar(255) DEFAULT NULL,
`status` varchar(255) NOT NULL,
`sub_status` varchar(255) DEFAULT NULL,
`shipment_id` varchar(100) NOT NULL,
`sku_quantity` varchar(100) DEFAULT NULL,
`sku_id` varchar(255) NOT NULL,
`delivery_date` varchar(255) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
`van_no` varchar(255) DEFAULT NULL,
`attempts` varchar(255) DEFAULT NULL,
`rescheduled_date` varchar(255) DEFAULT NULL,
`rescheduled_timeslote` varchar(255) DEFAULT NULL,
`last_modified_date` varchar(255) NOT NULL,
`latitude` varchar(255) DEFAULT NULL,
`longitude` varchar(255) DEFAULT NULL,
`priority` varchar(255) NOT NULL,
`delivery_partner_name` varchar(255) DEFAULT NULL,
`mode_transport` varchar(255) DEFAULT NULL,
`delivery_boy_name` varchar(255) DEFAULT NULL,
`vehicle_no` varchar(255) DEFAULT NULL,
`pal_pincode` varchar(255) NOT NULL,
`pal_area` varchar(255) NOT NULL,
`pal_location` varchar(255) NOT NULL,
`tag` varchar(255) DEFAULT NULL,
`dmk` varchar(255) DEFAULT NULL,
`route` varchar(255) NOT NULL,
`route_type` varchar(255) DEFAULT NULL,
`payment_type` varchar(255) DEFAULT NULL,
`order_type` varchar(100) DEFAULT NULL,
`club_order_id` varchar(100) DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp(),
`updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`orderupdated` int(1) NOT NULL DEFAULT 0,
`plan_id` varchar(200) DEFAULT NULL,
`utm_source` varchar(200) DEFAULT NULL,
`sku_mrp` varchar(20) DEFAULT NULL,
`warehouse_id` int(11) DEFAULT NULL,
`user_address_id` bigint(20) DEFAULT NULL,
`delivery_slot` varchar(10) DEFAULT NULL,
`eta_delivery_date` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `order_id` (`order_id`),
KEY `status` (`status`),
KEY `delivery_date` (`delivery_date`),
KEY `product_name` (`product_name`),
KEY `pal_area` (`pal_area`),
KEY `orders_plan_id` (`plan_id`),
KEY `pal_location` (`pal_location`),
KEY `club_order_id` (`club_order_id`),
KEY `orders_shipment_id` (`shipment_id`),
KEY `orders_order_id_pan_id` (`order_id`,`plan_id`),
KEY `orders_created_at` (`created_at`),
KEY `orders_billed_amount` (`billed_amount`),
KEY `orders_cin` (`customer_cin`),
KEY `idx_plan_order_id` (`plan_id`,`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=165726520 DEFAULT CHARSET=utf8
Table: routes
Create Table: CREATE TABLE `routes` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`route_type` varchar(10) DEFAULT NULL,
`created_at` datetime NOT NULL DEFAULT current_timestamp(),
`updated_at` datetime NOT NULL DEFAULT current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `name_2` (`name`),
KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=136730 DEFAULT CHARSET=utf8
************************** 1. row ***************************
Table: allotment_route_details
Create Table: CREATE TABLE `allotment_route_details` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`route_allocation_id` int(11) DEFAULT NULL,
`route_id` int(11) DEFAULT NULL,
`plan_id` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT current_timestamp(),
`modified_date` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`warehouse_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `route_allocation_id` (`route_allocation_id`),
KEY `route_id` (`route_id`),
KEY `plan_id` (`plan_id`),
KEY `idx_created_date` (`created_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1298186 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Table: warehouse_planid
Create Table: CREATE TABLE `warehouse_planid` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`warehouse_id` int(11) DEFAULT NULL,
`plan_id` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
`plan_title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
`slot` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
`process_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`delivery_date` datetime DEFAULT NULL,
`type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`status` int(11) DEFAULT 1,
`picklist_url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`created_date` datetime NOT NULL DEFAULT current_timestamp(),
`modified_date` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`reason` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`reason_details` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `wp_planid` (`plan_id`),
KEY `warehouse_id` (`warehouse_id`),
KEY `warehouse_planid_plan_id` (`plan_id`),
KEY `warehouse_plan_delivery_date` (`delivery_date`),
KEY `idx_created_date` (`created_date`)
) ENGINE=InnoDB AUTO_INCREMENT=226446 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Kindly help me on this, thanks!
CodePudding user response：
This looks like the "explode-implode" syndrome. This is where the query builds a big temp table from JOINs, then collapses it with a GROUP BY.
That is, do the minimum JOINing to get the id(s) for the table(s) in the GROUP BY, then reach into other tables (and possibly back into the original table(s) to get the other columns.
Without knowing which columns are in which tables, I cannot further help you.
Other tips... If "amount" and "rate" are numeric, and if you will be doing any computation with them, they should be some numeric type, not VARCHAR.
These may help now and after reformulating the query:
orders: INDEX(delivery_date, plan_id, pal_area)
cart: INDEX(order_id, out_of_stock)
routes: INDEX(name, id, route_type)
pr: INDEX(route_id, status, plan_id)
rra: INDEX(allotment_id, id, allotment_bts_date)
warehouse_planid: INDEX(warehouse_id, plan_id)
ard: INDEX(route_id, created_date, plan_id, route_allocation_id)
When adding a composite index, DROP index(es) with the same leading columns. That is, when you have both INDEX(a) and INDEX(a,b), toss the former.
CodePudding user response：
First, anytime you have a group by, you typically are required to include all non-aggregate columns as part of the group by. Of which, you only have a few values.
Having said that, if some things would never change, you could simply apply a MAX() or MIN() of that column.
Now your group by only includes: plan_id, pal_area, is_rvp, is_b2b, a.id But your data includes things like a user ID associated with some other deeper component, routes, dates, names. But if a given order under a given plan can cover different routes, how do you know you are pulling the final proper route in your data.
For indexes, I agree with the listing that Rick has suggested. That will help optimize the joins between tables ensuring the keys (in appropriate order) best match what you are shooting for.
One final suggestion, with MySQL, and I have encountered this many times, but does not always guarantee, add the clause STRAIGHT_JOIN which tells the engine to run the query in the order you have presented such as
select STRAIGHT_JOIN
(rest of query)