Poor performance sorting multiple tables in mysql 8.4

I've been trying to improve my query performance for a days in my db Mysql(8.4) but I'm having performance issues with the follow queries (I tried these both):

SELECT sql_no_cache * FROM infracao.multas m
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
    AND m.Renavam IN (SELECT Renavam FROM veiculo.VeiculoCliente vc WHERE vc.Renavam = m.Renavam AND vc.Cliente = 'CLT00188')
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000, 1000;

-- or

SELECT sql_no_cache * FROM infracao.multas m
JOIN veiculo.VeiculoCliente vc
    ON vc.Renavam = m.Renavam
    AND vc.Cliente = 'CLT00188'
JOIN infracao.multasDataBase mdb 
    ON mdb.MultaId = m.Id 
    AND mdb.DtBase = '2025-01-01'
ORDER BY m.CollectedAt DESC, m.Id
LIMIT 1000, 1000;

The index of these tables are the follow:

CREATE TABLE `VeiculoCliente` (
   `Renavam` varchar(11) NOT NULL DEFAULT '',
   `Cliente` varchar(8) NOT NULL DEFAULT '',
   `DtInsercao` datetime DEFAULT CURRENT_TIMESTAMP,
   PRIMARY KEY (`Cliente`,`Renavam`),
   KEY `Idx_Renavam` (`Renavam`),
   KEY `Idx_Cliente` (`Cliente`) /*!80000 INVISIBLE */
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 190k rows

CREATE TABLE `multasDataBase` (
   `MultaId` char(36) NOT NULL DEFAULT '',
   `DtBase` date NOT NULL,
[...]
   PRIMARY KEY (`DtBase`,`MultaId`),
   KEY `DtBaseIdx` (`DtBase`) /*!80000 INVISIBLE */,
   KEY `Fk_infracao_infracaoDtBase_idx` (`MultaId`),
   KEY `multasDataBase_Id_DtBaseIdx` (`DtBase` DESC,`MultaId`),
   CONSTRAINT `Fk_infracao_infracaoDtBase` FOREIGN KEY (`MultaId`) REFERENCES `multas` (`Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 2.4 milion rows

CREATE TABLE `multas` (
   `Id` char(36) NOT NULL DEFAULT '',
[...]
   `CollectedAt` datetime NOT NULL,
[...]
   PRIMARY KEY (`Id`),
   UNIQUE KEY `AitDetranGuia` (`AitDetran`,`Guia`),
   UNIQUE KEY `unique_InfracaoKeySne` (`InfracaoKeySne`),
   KEY `Renainf` (`Renainf`),
   KEY `RenavamIdx` (`Renavam`),
   KEY `InsertAtIdx` (`InsertedAtUtc`),
   KEY `NormalizedAitIdx` (`NormalizedAit`),
   KEY `AitSne` (`AitSne`),
   KEY `CollectedAtIdxDesc` (`CollectedAt`),
   KEY `multasOrgaoIdx` (`CodigoOrgao`),
   KEY `multa_Id_CollectedAtIdxDesc` (`Id`,`CollectedAt`),
   KEY `query_clientMultas_collectedat_idx` (`Renavam`,`CollectedAt` DESC, `Id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 708k rows

And that's the execution plan:

id,select_type,table,partitions,type,possible_keys,key,key_len,ref,rows,filtered,Extra
1,SIMPLE,vc,NULL,ref,"PRIMARY,Idx_Renavam",PRIMARY,10,const,3126,100.00,"Using index; Using temporary; Using filesort"
1,SIMPLE,m,NULL,ref,"PRIMARY,RenavamIdx,multa_Id_CollectedAtIdxDesc,query_clientMultas_collectedat_idx",RenavamIdx,13,veiculo.vc.Renavam,5,100.00,NULL
1,SIMPLE,mdb,NULL,eq_ref,"PRIMARY,Fk_infracao_infracaoDtBase_idx,multasDataBase_Id_DtBaseIdx",PRIMARY,39,"const,infracao.m.Id",1,100.00,NULL

The largest number of rows is situated in infracao.multas, and I need to sort by CollectedAt. As you can see in the execution plan, I'm getting the issue Using temporary; Using filesort, which slows down a lot the query performance.

Does anyone have an idea what I should do that could help with the query performance?

Solution

KEY `CollectedAtIdxDesc` (`CollectedAt`),

The name of this index ends with "Desc" so I guess you intended it to be a descending index. But it is not declared as a descending index. So it will not be used for your ORDER BY CollectedAt DESC.... You should define the index DESC if you intend to use it for descending sort order.

The order of rows returned by EXPLAIN is the join order, which the optimizer may change, so it is not necessarily the same order as the joins in your query. In your case, the optimizer did change the join order to vc, m, mdb.

But only the index of the first table in the join order (the real join order, after the optimizer has changed it) can be used to eliminate "Using filesort." In your case, the index that corresponds to your ORDER BY is in the second table, so it can't be used.

You can override the optimizer's choice of join order and force m to be first in the join order this way:

SELECT /*+ JOIN_ORDER(m,vc,mdb) */ *
FROM multas m 
JOIN ...rest of query...

In older versions of MySQL:

SELECT STRAIGHT_JOIN *
FROM multas m 
JOIN ...rest of query...