Mysql query not optimized (union all query)

RRQ
February 26, 2023
127 views
1 vote
2 Answers

here i need an help like how to optimize this union all query the table has index for where clause column but it’s not picking it up and i even tried with force index none of that works any better way to optimize it,

Edited;

Query;

select 
  retailerCode, 
  coalesce(rules_GM, GM) as GM, 
  year, 
  taxableValue, 
  category, 
  date 
from 
  margin 
where 
  date not in (
    select 
      distinct date 
    from 
      margin_mis)
 AND date >= '2019-01-01'  
union all 
  
    select 
      retailerCode, 
      coalesce(updated_GM, rules_GM) as GM, 
      year, 
      taxableValue, 
      category, 
      date 
    from 
      margin_mis
  WHERE date >= '2019-01-01';

explain plan;

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: margin
   partitions: NULL
         type: ALL
possible_keys: idx_date_invoiceNumber,date
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5931014
     filtered: 50.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: margin_mis
   partitions: NULL
         type: index
possible_keys: idx_date_invoiceNumber,idx_date
          key: idx_date
      key_len: 4
          ref: NULL
         rows: 3756279
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 3
  select_type: UNION
        table: margin_mis
   partitions: NULL
         type: ALL
possible_keys: idx_date_invoiceNumber,idx_date
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3756279
     filtered: 50.00
        Extra: Using where
3 rows in set, 1 warning (0.02 sec)

Table structure;

    Table: margin
Create Table: CREATE TABLE `margin` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `Indexing` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `margin` double DEFAULT NULL,
  `COGS` double DEFAULT NULL,
  `GM` double DEFAULT NULL,
  `retailerCode` varchar(250) DEFAULT NULL,
  `status` varchar(250) DEFAULT NULL,
  `originalAdnMedicineId` int DEFAULT NULL,
  `orderTimeStamp` datetime DEFAULT NULL,
  `marginSign` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `rules_margin` double DEFAULT NULL,
  `rules_COGS` double DEFAULT NULL,
  `rules_GM` double DEFAULT NULL,
  `batchId` varchar(255) DEFAULT NULL,
  `orderType` varchar(255) DEFAULT NULL,
  `mappedBDRetailerCode` varchar(255) DEFAULT NULL,
  `grn_COGS` double DEFAULT NULL,
  `tax` int DEFAULT NULL,
  `orderRetrieveId` int DEFAULT NULL,
  `expected_COGS` double DEFAULT NULL,
  `grn_itemName` varchar(255) DEFAULT NULL,
  `lenderName` varchar(255) DEFAULT NULL,
  `grn_tax` int DEFAULT NULL,
  `expected_GM` double DEFAULT NULL,
  `grnId` int DEFAULT NULL,
  `billType` varchar(255) DEFAULT NULL,
  `grn_packaging` varchar(255) DEFAULT NULL,
  `grn_tcs` double DEFAULT NULL,
  `grn_rules_GM` double DEFAULT NULL,
  `grn_quantity` int DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `hubCode` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_date_invoiceNumber` (`Date`,`invoiceNumber`),
  KEY `category` (`category`),
  KEY `Manufacturer` (`manufacturerName`),
  KEY `Dist` (`distributorId`),
  KEY `Req_hub_id` (`requestingHubId`),
  KEY `MediicneId` (`uniqueCode`),
  KEY `Ser_Hub_id` (`servicingHubId`),
  KEY `medicineCategory` (`medicineCategory`),
  KEY `rules_GM` (`rules_GM`),
  KEY `year` (`year`),
  KEY `month` (`month`),
  KEY `mapedBDRetailerCode` (`mappedBDRetailerCode`),
  KEY `date` (`Date`),
  KEY `retrieval_spoke_code` (`retrievalSpokeCode`),
  KEY `idx_mappedBDRetailerCode_month` (`mappedBDRetailerCode`,`month`),
  KEY `idx_BUYER_NET_VALUE_mappedBDRetailerCode` (`BUYER_NET_VALUE`,`mappedBDRetailerCode`),
  KEY `idx_BUYER_NET_VALUE_retailerCode_mappedBDRetailerCode` (`BUYER_NET_VALUE`,`retailerCode`,`mappedBDRetailerCode`),
  KEY `idx_com` (`month`,`year`)
) ENGINE=InnoDB AUTO_INCREMENT=8687695 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci



*************************** 1. row ***************************
       Table: margin_mis
Create Table: CREATE TABLE `margin_mis` (
  `_id` int unsigned NOT NULL AUTO_INCREMENT,
  `Indexing` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
   `margin` double DEFAULT NULL,
  `COGS` double DEFAULT NULL,
  `GM` double DEFAULT NULL,
  `manufacturerName` varchar(250) DEFAULT NULL,
  `medicineSubSectionType` varchar(250) DEFAULT NULL,
  `retailerCode` varchar(250) DEFAULT NULL,
   `status` varchar(250) DEFAULT NULL,
   `purchaseDetailsinvoiceNumber` varchar(255) DEFAULT NULL,
  `adnId` int DEFAULT NULL,
  `mappedBDRetailerCode` varchar(255) DEFAULT NULL,
  `orderRetrieveId` int DEFAULT NULL,
  `igst` int DEFAULT NULL, .....

  `grn_margin` double DEFAULT NULL,
  `margin_cn_retail` double DEFAULT NULL,
  `margin_cn_carin` double DEFAULT NULL,
  `margin_cn_wholesale` double DEFAULT NULL,
  PRIMARY KEY (`_id`),
  KEY `idx_date_invoiceNumber` (`Date`,`invoiceNumber`),
  KEY `category` (`category`),
  KEY `Manufacturer` (`manufacturerName`),
  KEY `Dist` (`distributorId`),
  KEY `Req_hub_id` (`requestingHubId`),
  KEY `MediicneId` (`uniqueCode`),
  KEY `Ser_Hub_id` (`servicingHubId`),
  KEY `idx_date` (`Date`)
) ENGINE=InnoDB AUTO_INCREMENT=3908752 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

If there are any rewrite options, kindly suggest them and share your thoughts here.

Answers

I can’t understand, why you have a derived table in the first place, soyou can get rid of it.

also do you really want that many indexes in the first place, or have you added to cover all possibiities, so iwould recomend toget rid of the most, and add some combined ones where needed

select 
  retailerCode, 
  coalesce(rules_GM, GM) as GM, 
  year, 
  taxableValue, 
  category, 
  date 
from 
  margin 
where 
  date not in (
    select 
      distinct date 
    from 
      margin_mis)
 AND date >= '2019-01-01'  
union all 
  
    select 
      retailerCode, 
      coalesce(updated_GM, rules_GM) as GM, 
      year, 
      taxableValue, 
      category, 
      date 
    from 
      margin_mis
  WHERE date >= '2019-01-01';

If you have lots of data, NOT IN is not that fast, so you can try

select 
  margin.retailerCode, 
  coalesce(margin.rules_GM, margin.GM) as GM, 
  margin.year, 
  margin.taxableValue, 
  margin.category, 
  margin.date 
from 
  margin JOIN margin_mis ON marign.date <> margin_mis.date
where 
   margin.date >= '2019-01-01'  
union all 
  
    select 
      retailerCode, 
      coalesce(updated_GM, rules_GM) as GM, 
      year, 
      taxableValue, 
      category, 
      date 
    from 
      saveo_analytics.margin_mis
  WHERE date >= '2019-01-01'

- RickJames
- February 27, 2023 at 1:44 am
- 0 votes
0
Instead of
```
date not in (
    select 
      distinct date 
    from 
      margin_mis)
```
do
```
NOT EXISTS ( SELECT 1 FROM margin_mis
                 WHERE margin_mis.date = margin.date )
```
What is the datatype of Date? (Hopefully it is DATE.)

It is usually better to extract year and month as needed, rather than having the as separate columns.

Whenever you have INDEX(a,b) do not also have INDEX(a)
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.