Home > Enterprise >  While the server is under more strain, queries become slower
While the server is under more strain, queries become slower

Time:12-29

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)
  • Related