Melhorar a performance desa consulta

Infraestrutura Banco de Dados
#1

Bom dia galera, já começando o ano com uma pergunta de banco para começar bem o ano.
Estou querendo melhora o tempo de resposta dessa consulta, hoje o resultado leva em média 13 segundos para mostrar o resultado.
A tabela principal possui mais de 2.000.000, de registros.
O resultado é de apenas 34 linhas.
As tabelas possuem índices.
O capo r.date_gps também está indexados.
É consulta é para mostrar a última comunicação.

SELECT r.id,CONCAT(v.title,' ',v.plaque) AS title,v.type_vehicle,r.sn,r.lat,r.lng,r.direction,r.date_db,r.date_gps ,r.volt_main,r.speed,r.ign,r.sat_fix,r.sat FROM registers r JOIN trackers t ON t.cod = r.sn JOIN installations i ON t.id = i.trackers_id JOIN vehicles v ON v.id = i.vehicles_id JOIN clients c ON c.id = v.clients_id WHERE r.date_gps = ( SELECT MAX(rr.date_gps) FROM registers rr WHERE r.sn = rr.sn ) AND c.management_id = 1 GROUP BY r.sn ORDER BY r.date_gps DESC;

#2

Difícil dizer assim sem conhecer os índices das tabelas e qual o banco de dados.

Se for MySQL, use o EXPLAIN para tentar descobrir o problema:
http://dev.mysql.com/doc/refman/5.7/en/explain.html

#3

Esse é o EXPLAIN

explain.png1220×137 10.4 KB

#4

A tabela principal é o registers? Esse SN tem quais indices? E do date_gps?

#5

Registers é a tabela principal.
sn é um varchar, o índice com mesmo nome
date_gps é datetime, o índice com mesmo nome

Antes usava 2 query para o mesmo resultado só que não utilizava o MAX() no date_gps e o resultado é extremamente mais rápido, só que as vezes não vinha o registro mais atual, isso com um FOR no PHP.
Mas quando coloco o MAX() no date_gps ocorre a mesma demora.
Queria se possível usar 1 query e retornasse mais rápido.