Fórum Como poderia otimizar esta query ? #576562

olá bom dia,

como poderia otimizar esta query:

SELECT C.*, Team.Team, Category.Description FROM classifications AS C
INNER JOIN eventparticipants AS Team ON Team.id = C.participantid AND Team.eventid = 30
INNER JOIN categories AS Category ON Category.id = C.categoryid AND Category.eventid = 30
INNER JOIN competition AS Competition ON Competition.id = C.competitionid AND Competition.eventid = 30
WHERE C.eventid = 30 AND C.Lap <> 0 AND (C.eventid, C.participantid, C.lap)
IN (SELECT CC.eventid, CC.participantid, MAX(CC.lap)
FROM classifications AS CC GROUP BY CC.eventid, CC.participantid)
GROUP BY C.participantid
ORDER BY C.competitionid, C.lap DESC, C.GlobalPos, C.globaltime ASC

A mostrar registos de 0 - 29 (2502 total, O Query demorou 36.6981 sec)

a tabela classifications tem: 10.041 registos
a tabela eventparticipants tem: 11.256 registos
a tabela competition: 85 registos
a tabela categories: 115 registos

obrigado


Essa dúvida foi gerada a partir de um comentário no artigo Otimização de Consultas SQL