Optimizing MySQL query with INNER JOINS, LEFT JOINS, GROUP BY and HAVING

Question

I'm having trouble optimizing this really big query and I can't change the table structure except creating additional indexes and small adjustments.

SELECT
    'Fattura Prodotti Postali' AS `type`,
    SUM(dpd.qta) AS `products_count_quantity`,
    COUNT(dpd.IDlavorazione_dett) AS `products_count`,
    dpd.IDlavorazione_dett AS `IDlavorazione_dett`,
    dp.prod_totali- CAST(dp.opzione1 AS UNSIGNED) AS `process_products_count`,
    dp.IDdistinta AS `product_code`,
    dp.data_distinta AS `process_date`,
    dp.IDesito AS `process_status_id`,
    dp.note AS `process_note`,
    dp.IDlavorazione AS `unique_id`,
    SUM(dpd.tariffa) AS `products_total`,
    SUM(IF(o.IDdoc IS NULL,1,0)*dpd.qta) AS `tobill_count_quantity`,
    SUM(IF(NOT o.IDdoc IS NULL,1,0)*dpd.qta) AS `billed_count_quantity`,
    SUM(IF(o.IDdoc IS NULL,1,0)) AS `tobill_count`,
    SUM(IF(o.IDdoc IS NULL, dpd.tariffa,0)) AS `tobill_total`,
    SUM(IF(NOT o.IDdoc IS NULL,1,0)) AS `billed_count`,
    SUM(IF(NOT o.IDdoc IS NULL, dpd.tariffa,0)) AS `billed_total`,
    SUM(IF(o.IDdoc IS NULL, dpd.tariffa,0)-IF(NOT o.IDdoc IS NULL, dpd.tariffa,0)) AS `bill_diff`,
    COUNT(dpd.IDlavorazione_dett) AS `products_count`,
    SUM(dpd.tariffa) AS `products_total`,
    SUM(dpd.tariffa*(dpd.iva/100)) AS `products_vat`,
    SUM(dpd.tariffa*(dpd.sconto/100.0)) AS `products_discount`,
    SUM(dpd.tariffa*(1.0-dpd.sconto/100.0)*(dpd.iva/100)) AS `products_discount_vat`,
    SUM(dpd.tariffa*(1+(dpd.iva/100.0))) AS `products_total_vat`,
    SUM(ROUND(dpd.tariffa*(1.0-dpd.sconto/100.0),5)) AS `products_total_discount`,
    SUM(dpd.tariffa*(1.0-dpd.sconto/100.0)*(1+(dpd.iva/100.0))) AS `products_total_discount_vat`,
    SUM(dpd.qta) AS `products_quantity`,
    SUM(IF(o.IDdoc IS NULL,1,0)) AS `tobill_count`,
    SUM(IF(o.IDdoc IS NULL, dpd.tariffa,0)) AS `tobill_total`,
    SUM(IF(o.IDdoc IS NULL, dpd.tariffa*(1+(dpd.iva/100.0)),0)) AS `tobill_total_vat`,
    SUM(IF(NOT o.IDdoc IS NULL,1,0)) AS `billed_count`,
    SUM(IF(NOT o.IDdoc IS NULL, dpd.tariffa,0)) AS `billed_total`,
    SUM(IF(NOT o.IDdoc IS NULL, dpd.tariffa*(1+(dpd.iva/100.0)),0)) AS `billed_total_vat`
FROM doc_prodottipostali_dett dpd
INNER JOIN tracking t ON (dpd.IDlavorazione_dett=t.product_id)
LEFT JOIN prodotti_pp ppp ON (dpd.IDprodotto=ppp.IDprodotto)
LEFT JOIN categorie_pp cpp ON (ppp.categoria=cpp.IDcategoria)
LEFT JOIN categorie_pp cppp ON (CAST(dpd.IDcategoria AS UNSIGNED)=cppp.IDcategoria)
INNER JOIN doc_prodottipostali dp ON (dpd.IDlavorazione=dp.IDlavorazione)
LEFT JOIN ordini o ON (dpd.IDfattura=o.IDdoc)
WHERE
(
    (dp.tipo = 'PT' AND t.date >= '2022-05-15 00:00:00' AND t.date <= '2022-07-26 23:59:59') AND
    ((IF(dpd.IDprodotto>0, cpp.codice, IF(CAST(dpd.IDcategoria AS UNSIGNED)>0, cppp.codice, 'NONE')) NOT IN ('LAW','AR','CAD','EMESSOCAD') OR IF(dpd.IDprodotto>0, cpp.codice, IF(CAST(dpd.IDcategoria AS UNSIGNED)>0, cppp.codice, 'NONE')) IS NULL)) AND
    ((t.last IN (-1,2,3,6,7,10,11,34,35,130,131,258,514,4098,4354,8194,8450))) AND
    (((dp.opzione2 = 'PI' AND dp.data_distinta < '2022-08-25')) OR ((dp.data_distinta >= '2022-08-25')) OR ((o.IDdoc >= '1'))) AND
    (((t.last IN (-1,2,3,6,7,10,11,34,35,130,131,258,514,4098,4354,8194,8450))))
) AND
(
    ((NOT t.last IN (-1,4,5,6,7,20,21,132,133,149,516,532,1028,1157,8197)))
)
GROUP BY dpd.IDlavorazione
HAVING (1=1 AND ((tobill_count > '0')))
ORDER BY dp.data_distinta ASC;

The create tables are as following:

CREATE TABLE `doc_prodottipostali_dett` (
  `IDlavorazione_dett` int(11) NOT NULL AUTO_INCREMENT,
  `IDlavorazione` int(11) NOT NULL,
  `IDdistinta` varchar(45) NOT NULL,
  `IDcategoria` varchar(45) NOT NULL,
  `IDprodotto` int(11) NOT NULL,
  `codiceabarre` varchar(255) NOT NULL,
  `codiceavviso` varchar(255) NOT NULL DEFAULT '',
  `IDvettore` int(11) NOT NULL,
  `IDlistino` int(11) NOT NULL,
  `rif` varchar(45) NOT NULL,
  `IDmittente` int(11) NOT NULL,
  `IDdestinatario` int(11) NOT NULL,
  `ufficio_mittente` varchar(45) NOT NULL,
  `nome_lavoro` varchar(255) NOT NULL,
  `IDpostino` int(11) DEFAULT NULL,
  `note` longtext NOT NULL,
  `allegati` char(1) NOT NULL,
  `utente` varchar(45) NOT NULL,
  `peso` decimal(10,5) NOT NULL,
  `tariffa` decimal(10,5) NOT NULL,
  `sconto` float NOT NULL DEFAULT 0,
  `iva` int(11) NOT NULL,
  `qta` int(11) NOT NULL,
  `am` char(1) NOT NULL,
  `cp` char(1) NOT NULL,
  `eu` char(1) NOT NULL,
  `aa` char(10) NOT NULL,
  `ee` char(10) NOT NULL,
  `stato` int(11) NOT NULL,
  `lavorato` int(1) NOT NULL,
  `IDesito` int(11) NOT NULL,
  `esito` varchar(45) NOT NULL,
  `data_op` datetime NOT NULL,
  `fatturato` int(11) NOT NULL,
  `data_fatt` date NOT NULL,
  `IDfattura` int(11) NOT NULL,
  `data` datetime DEFAULT NULL,
  `data_ar` datetime DEFAULT NULL,
  `nome_ar` varchar(255) DEFAULT NULL,
  `file_ar` varchar(255) DEFAULT NULL,
  `IDflusso_dett` int(11) NOT NULL DEFAULT 0,
  `type` varchar(2) NOT NULL DEFAULT '',
  `typology` varchar(2) NOT NULL DEFAULT '',
  `related_id` int(11) NOT NULL DEFAULT 0,
  `related_ar_id` int(11) NOT NULL DEFAULT 0,
  `unregistered` int(1) NOT NULL DEFAULT 0,
  `total_attachments` int(11) NOT NULL DEFAULT 0,
  `repeated_recipient` int(4) DEFAULT NULL,
  `law_tomanage` tinyint(4) NOT NULL DEFAULT 0,
  `law_towork` int(11) NOT NULL DEFAULT 0,
  `law_toprint` int(11) NOT NULL DEFAULT 0,
  `notlaw_tocomplete` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`IDlavorazione_dett`),
  KEY `IDvettore` (`IDvettore`),
  KEY `IDmittente` (`IDmittente`),
  KEY `IDdestinatario` (`IDdestinatario`),
  KEY `IDlavorazione` (`IDlavorazione`),
  KEY `IDfattura` (`IDfattura`),
  KEY `codiceabarre` (`codiceabarre`),
  KEY `rif` (`rif`),
  KEY `IDpostino` (`IDpostino`),
  KEY `IDcategoria` (`IDcategoria`),
  KEY `IDprodotto` (`IDprodotto`),
  KEY `IDlistino` (`IDlistino`),
  KEY `codiceavviso` (`codiceavviso`),
  KEY `IDflusso_dett` (`IDflusso_dett`),
  KEY `typology` (`typology`),
  KEY `nome_ar` (`nome_ar`),
  KEY `unregistered` (`unregistered`),
  KEY `related_ar_id` (`related_ar_id`),
  KEY `related_id` (`related_id`) USING BTREE,
  KEY `type` (`type`),
  KEY `stato` (`stato`),
  KEY `lavorato` (`lavorato`),
  KEY `law_tomanage` (`law_tomanage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tracking` (
  `tracking_id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `status_id` int(11) NOT NULL,
  `lat` double NOT NULL DEFAULT 0,
  `lng` double NOT NULL DEFAULT 0,
  `entity_id` int(11) NOT NULL,
  `sub_entity_id` int(11) NOT NULL DEFAULT 0,
  `date` datetime NOT NULL,
  `note` text DEFAULT NULL,
  `last` int(11) NOT NULL DEFAULT 0,
  `date_last` datetime NOT NULL DEFAULT current_timestamp(),
  `tracking_rel` int(11) NOT NULL DEFAULT 0,
  `quantity_from` int(11) NOT NULL DEFAULT 1,
  `quantity_to` int(11) NOT NULL DEFAULT 1,
  `price` decimal(14,4) NOT NULL DEFAULT 0.0000,
  `price_unit` decimal(14,4) NOT NULL DEFAULT 0.0000,
  `vat` decimal(14,4) NOT NULL DEFAULT 0.0000,
  `invoice_id` int(11) NOT NULL DEFAULT 0,
  `management_status` int(11) NOT NULL DEFAULT 0,
  `package_id` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`tracking_id`),
  KEY `product_id` (`product_id`),
  KEY `status_id` (`status_id`),
  KEY `entity_id` (`entity_id`),
  KEY `sub_entity_id` (`sub_entity_id`),
  KEY `date` (`date`),
  KEY `last` (`last`),
  KEY `package_id` (`package_id`),
  KEY `management_status` (`management_status`),
  KEY `date_last` (`date_last`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `prodotti_pp` (
  `IDprodotto` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `codice` varchar(255) NOT NULL DEFAULT '',
  `codiceabarre` varchar(255) NOT NULL DEFAULT '',
  `descrizione` longtext NOT NULL,
  `prezzo` decimal(10,2) NOT NULL DEFAULT 0.00,
  `prezzo_2` decimal(10,2) NOT NULL DEFAULT 0.00,
  `prezzo_3` decimal(10,2) NOT NULL DEFAULT 0.00,
  `UM` char(3) NOT NULL DEFAULT '',
  `peso` decimal(10,2) NOT NULL DEFAULT 0.00,
  `datapv` date NOT NULL DEFAULT '0000-00-00',
  `iva` decimal(10,0) NOT NULL DEFAULT 0,
  `fornitore` varchar(45) NOT NULL DEFAULT '',
  `categoria` int(10) unsigned NOT NULL DEFAULT 0,
  `trasp` decimal(10,2) NOT NULL DEFAULT 0.00,
  `dettaglio` longtext NOT NULL,
  `ico` varchar(255) NOT NULL DEFAULT '',
  `foto` varchar(255) NOT NULL DEFAULT '',
  `visibility_web` char(1) NOT NULL DEFAULT '',
  `composito` char(1) NOT NULL DEFAULT '',
  `sottocat` int(10) unsigned NOT NULL DEFAULT 0,
  `tipologia` int(10) NOT NULL DEFAULT 0,
  `marchio` int(10) unsigned NOT NULL DEFAULT 0,
  `disp` char(1) NOT NULL DEFAULT '',
  `vetrina1` char(1) NOT NULL DEFAULT '',
  `vetrina2` char(1) NOT NULL DEFAULT '',
  `click` decimal(10,0) NOT NULL DEFAULT 0,
  `IDnote` int(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (`IDprodotto`),
  KEY `categoria` (`categoria`),
  KEY `codice` (`codice`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `categorie_pp` (
  `IDcategoria` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IDmadre` int(11) NOT NULL DEFAULT 0,
  `codice` varchar(255) NOT NULL,
  `nome` varchar(45) NOT NULL DEFAULT '',
  `visibility_web` char(1) NOT NULL DEFAULT '',
  PRIMARY KEY (`IDcategoria`),
  KEY `codice` (`codice`),
  KEY `IDmadre` (`IDmadre`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;


CREATE TABLE `doc_prodottipostali` (
  `IDlavorazione` int(11) NOT NULL AUTO_INCREMENT,
  `codice_lavorazione` varchar(255) NOT NULL,
  `IDdistinta` varchar(45) NOT NULL,
  `tipo` varchar(45) NOT NULL,
  `data_distinta` date NOT NULL,
  `data_lavorazione` date NOT NULL,
  `IDcliente` int(11) DEFAULT 1,
  `IDpagamento` int(11) NOT NULL,
  `note` longtext NOT NULL,
  `utente` varchar(45) NOT NULL,
  `stato` int(11) NOT NULL,
  `data_op` datetime NOT NULL,
  `opzioni` varchar(45) NOT NULL,
  `rif` varchar(45) NOT NULL,
  `opzione1` varchar(45) NOT NULL,
  `opzione2` varchar(45) NOT NULL,
  `allegati` int(11) NOT NULL DEFAULT 0,
  `IDesito` int(11) NOT NULL,
  `esito` varchar(45) NOT NULL,
  `prod_totali` int(11) NOT NULL,
  `prod_accettati` int(11) NOT NULL,
  `prod_fatturati` int(11) NOT NULL,
  `prod_chiusi` int(11) NOT NULL,
  `prod_end_shipping` int(11) NOT NULL,
  `stato_fatt` char(1) NOT NULL,
  `IDfattura` int(11) NOT NULL DEFAULT 0,
  `IDrel` int(11) NOT NULL DEFAULT 0,
  `IDflow` int(11) NOT NULL DEFAULT 0,
  `laws_tomanage` tinyint(4) NOT NULL DEFAULT 0,
  `idSender` int(11) NOT NULL,
  PRIMARY KEY (`IDlavorazione`),
  KEY `IDdistinta` (`IDdistinta`),
  KEY `IDfattura` (`IDfattura`),
  KEY `IDrel` (`IDrel`),
  KEY `allegati` (`allegati`),
  KEY `tipo` (`tipo`),
  KEY `IDesito` (`IDesito`),
  KEY `laws_tomanage` (`laws_tomanage`),
  KEY `data_distinta` (`data_distinta`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `ordini` (
  `IDdoc` int(11) NOT NULL AUTO_INCREMENT,
  `IDsoggetto` int(11) DEFAULT 1,
  `numero` varchar(45) NOT NULL DEFAULT '0',
  `numero_web` int(11) NOT NULL,
  `datadoc` date NOT NULL DEFAULT '0000-00-00',
  `tipodoc` varchar(255) NOT NULL,
  `type_doc` varchar(4) NOT NULL DEFAULT '',
  `modpag` int(10) unsigned NOT NULL DEFAULT 0,
  `div_dest` longtext NOT NULL,
  `IDagente` varchar(6) NOT NULL DEFAULT '0',
  `IDvettore` int(10) unsigned NOT NULL DEFAULT 0,
  `imballo` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `colli` decimal(10,0) NOT NULL DEFAULT 0,
  `datascad` date NOT NULL DEFAULT '0000-00-00',
  `stato` char(1) NOT NULL DEFAULT '',
  `note` longtext NOT NULL,
  `importo` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `tipo` varchar(4) NOT NULL DEFAULT '',
  `causale` varchar(45) NOT NULL DEFAULT '',
  `azione` char(1) NOT NULL DEFAULT '',
  `utente` varchar(45) NOT NULL DEFAULT '',
  `data_op` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `acconto` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `iva` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `sconto` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `n_doc_passive` varchar(45) NOT NULL DEFAULT '',
  `porto` varchar(45) NOT NULL DEFAULT '',
  `dataora_rit` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `ordine_web` char(1) NOT NULL DEFAULT '',
  `clonato` char(1) NOT NULL DEFAULT '',
  `trasp` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `varie` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `banca_appo` int(11) NOT NULL DEFAULT 0,
  `IDmagazzino` int(11) NOT NULL DEFAULT 0,
  `pv` int(11) NOT NULL DEFAULT 0,
  `fornitore` int(11) NOT NULL DEFAULT 0,
  `qta_colli` decimal(10,0) NOT NULL DEFAULT 0,
  `bolli` decimal(20,5) NOT NULL DEFAULT 0.00000,
  `agibilitamezzi` varchar(255) NOT NULL DEFAULT '',
  `opzioneprezzo` varchar(45) NOT NULL DEFAULT '',
  `emailcc` varchar(255) NOT NULL DEFAULT '',
  `U_IBAN` varchar(45) NOT NULL,
  `stato_fatt` char(1) NOT NULL,
  `IDfattura` int(11) NOT NULL,
  `data_fatt` date NOT NULL,
  `digital_idinvoice` varchar(255) NOT NULL DEFAULT '0',
  `digital_idupload` bigint(11) NOT NULL DEFAULT 0,
  `digital_status` int(11) NOT NULL DEFAULT 0,
  `digital_type` varchar(2) NOT NULL DEFAULT '',
  `digital_office` int(11) NOT NULL,
  `digital_sectional` char(2) NOT NULL DEFAULT '',
  `digital_last_office` int(11) NOT NULL,
  `digital_flag` varchar(4) NOT NULL,
  `determines_code` varchar(255) NOT NULL,
  `determines_date` date NOT NULL,
  `determines_id` varchar(255) NOT NULL,
  `flag_2` varchar(255) NOT NULL,
  `flag` int(11) NOT NULL,
  `flag_note` longtext NOT NULL,
  `flag_3` varchar(255) NOT NULL,
  `type_op` varchar(255) NOT NULL,
  `IDpartenza_rel` int(11) NOT NULL,
  `IDdestinazione_rel` int(11) NOT NULL,
  `doc_insinuazionepassivo` varchar(255) NOT NULL,
  `doc_insinuazionepassivo_data` date NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY (`IDdoc`),
  KEY `IDsoggetto` (`IDsoggetto`),
  KEY `IDfattura` (`IDfattura`),
  KEY `pv` (`pv`),
  KEY `azione` (`azione`),
  KEY `numero` (`numero`),
  KEY `tipodoc` (`tipodoc`),
  KEY `datadoc` (`datadoc`),
  KEY `digital_sectional` (`digital_sectional`),
  KEY `digital_type` (`digital_type`),
  KEY `digital_office` (`digital_office`),
  KEY `digital_status` (`digital_status`),
  KEY `type_doc` (`type_doc`),
  KEY `flag_2` (`flag_2`),
  KEY `flag` (`flag`),
  KEY `flag_3` (`flag_3`),
  KEY `type_op` (`type_op`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

These are the number of rows for each table:

tracking:42231628
doc_prodottipostali_det:11316150
doc_prodottipostali:40556
ordini:40360
prodotti_pp:52
categorie_pp:30

I've tried making a subquery on the tracking table (the biggest), trying to filter out most of the rows but it didn't work.

This is the explain I get, it looks fine except for the "Using temporary" and "Using filesort".

The problem with the query is that with low numbers of elaborated rows it runs fine, the moment it tries to elaborate more than 1 million it begins to slow down.

Explain

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t	range	product_id,date,last	date	5	\N	69266	Using index condition; Using where; Using temporary; Using filesort
1	SIMPLE	dpd	eq_ref	PRIMARY,IDlavorazione	PRIMARY	4	integraa_20220508.t.product_id	1
1	SIMPLE	ppp	eq_ref	PRIMARY	PRIMARY	4	integraa_20220508.dpd.IDprodotto	1	Using where
1	SIMPLE	cpp	eq_ref	PRIMARY	PRIMARY	4	integraa_20220508.ppp.categoria	1	Using where
1	SIMPLE	cppp	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where
1	SIMPLE	o	eq_ref	PRIMARY	PRIMARY	4	integraa_20220508.dpd.IDfattura	1	Using index
1	SIMPLE	dp	eq_ref	PRIMARY,tipo,data_distinta	PRIMARY	4	integraa_20220508.dpd.IDlavorazione	1	Using where

When you try to extend the time range on the tracking table t.date >= '2022-05-15 00:00:00' AND t.date <= '2022-07-26 23:59:59' the optimizer switches to a full table scan. I've tried forcing the 'date' index on the tracking table but it still slows down with big ranges. The users are supposed to search without the time range too, so an index on the date isn't really the best option.