Mysql query not optimized (union all query)

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.

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'

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)