Poor performance sorting multiple tables in mysql 8.4

Question

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?

it's probably the ORDER BY fault. instead of * try to select only columns you need. — IT goldman
– IT goldman, Commented Jun 22 at 14:54
Column selections doesn't affect the filesort. I've just tried SELECT sql_no_cache m.CollectedAt, m.Id [..] and happened the same. @ITgoldman — Guilherme Bley
– Guilherme Bley, Commented Jun 22 at 15:00
You are aware that the 1000,1000 gets the second set of 1000 rows, not the first, right? — ysth
– ysth, Commented Jun 24 at 15:27

Bill Karwin · Accepted Answer · 2025-06-22 15:44:37Z

2

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...

edited Jun 22 at 15:44

answered Jun 22 at 15:29

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Guilherme Bley Jun 23 at 16:37

Thanks for your answer, this way I could realize that it isn't the filesort that is slowing down the query, but the column quantity. The query executor is providing the best performance, when I use the /*+ JOIN_ORDER(m,vc,mdb) */ the query becomes slower than usual.

Bill Karwin Jun 23 at 17:45

Yes, it's rare that the optimizer makes the wrong choice.

Altxxr0 · Accepted Answer · 2025-06-22 17:46:39Z

1

Your ORDER BY is on a joined large dataset (multas has 708k rows) causes Using temporary; Using filesort also JOIN before LIMIT Sql needs to fully join and sort before applying the LIMIT, and your ORDER BY fields don't match the best index for sorting, Pagination with LIMIT offset, size becomes worse with high offsets in (1000, 1000).

Try This:

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

Your main fix is pushing ORDER BY + LIMIT into a derived table to avoid sorting a large joined set.

edited Jun 22 at 17:46

answered Jun 22 at 15:47

Altxxr0

2921 silver badge11 bronze badges

2 Comments

Guilherme Bley Jun 23 at 16:39

Unfortunelly the table infracao.multas has too many rows to do this action. And limiting before, the next filter that will be made in infracao.multasDataBase won't be respected. Thanks for the answer, I could see where I'm doing wrong but I will give u an up-vote.

Rick James Aug 4 at 4:19

Pushing that into the derived table is good but it fails to do all the filtering that is being done on other tables.

ysth · Accepted Answer · 2025-06-24 14:41:09Z

1

It looks to me like your query is mostly doing the right thing, finding the mdb and vc rows and filtering m rows based on them. Ordering by a column in one large table but filtering based on two other largish tables is going to be slow. But you should try adding an additional index to m:

alter table multas add index `query_collectedatdesc_renavam` (CollectedAt DESC, Renavam)

so as it is finding the most recent 2000 qualifying records it can filter using the Renavam and Id (primary key is automatically added to innodb indexes) while just reading the index (but do verify with EXPLAIN that the new index is actually being used).

answered Jun 24 at 14:41

ysth

99.1k6 gold badges126 silver badges219 bronze badges

2 Comments

Guilherme Bley Jun 25 at 9:46

Unfortunately, this index didn't change the query execution and was not used. But thanks for your time answering; I'll give an upvote.

ysth Jun 25 at 14:43

Try making it use the index; rearrange the query to have m joined last and after as m add force index (query_collectedatdesc_renavam) (or whatever name you gave it or it defaulted to)

Rick James · Accepted Answer · 2025-08-04 04:18:37Z

0

    mdb:  INDEX(MultaId,  DtBase)
    mdb:  INDEX(DtBase , Id)
    vc:  INDEX(Renavam, Cliente)
    m:  INDEX(Renavam)
    m:  INDEX(CollectedAt, Id)

Change `ORDER BY  m.CollectedAt DESC, m.Id` to `ORDER BY  m.CollectedAt DESC, m.Id DESC`.
(That will help in any version.)
Since you are running 8.4, this is an alternative:
    INDEX(CollectedAt DESC, Id ASC)

Get rid of `KEY `multa_Id_CollectedAtIdxDesc` (`Id`,`CollectedAt`)` since Id is the PK.

Get rid of `KEY `RenavamIdx` (`Renavam`),` since there is another index starting with the same column.

answered Aug 4 at 4:18

Rick James

144k15 gold badges144 silver badges255 bronze badges

Collectives™ on Stack Overflow

Poor performance sorting multiple tables in mysql 8.4

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related