INNER JOIN Group Too slow in the query

Question

INNER JOIN Group Too slow in the query

Navigation

#1 by (2 votes)

1

I have a select in mysql that is taking a long time to return the records, I noticed that this happens after the table gets fuller.

This select makes 4 INNER JOINS and takes the last record of each id belonging to the id_motorist column with a GROUP , the script works perfectly but is very slow.

Is there any way to improve this and make it faster, below is my script

$result=mysql_query("SELECT 
  g.idgps,g.lat,g.long,g.id_motorista,g.id_saida,a.start,a.hora_start,a.p_nome,a.p_tipo_veiculo,a.id_saida,a.title,a.id_veiculo,v.modelo,v.placa,v.cor,v.foto1,v.id_veiculo,m.nomecompleto,m.id,m.logo, 

 'idgps' FROM gps AS g
  INNER JOIN agenda_saidas AS a
  ON g.id_saida= a.id_saida 
  INNER JOIN veiculos AS v
  ON a.id_veiculo = v.id_veiculo 
  INNER JOIN motoristas AS m
  ON g.id_motorista = m.id 
  WHERE 'idgps' IN (SELECT MAX('idgps')FROM gps GROUP BY id_motorista)");

php mysql

asked by anonymous 27.09.2015 / 03:08

1 answer

Query with query using LINQ and NHibernate Tie of Repetition in Tmemo

score 2 · Answer 1

SELECT
  MAX(g.idgps), /*Esta agregação faz parte da mágica*/
  g.lat,
  g.long,
  g.id_motorista,
  g.id_saida,
  a.start,
  a.hora_start,
  a.p_nome,
  a.p_tipo_veiculo,
  a.id_saida,
  a.title,
  a.id_veiculo,
  v.modelo,
  v.placa,
  v.cor,
  v.foto1,
  v.id_veiculo,
  m.nomecompleto,
  m.id,
  m.logo
  /*'idgps' -> Não entendi, então comentei*/
FROM gps g
INNER JOIN agenda_saidas a  ON g.id_saida = a.id_saida 
INNER JOIN veiculos      v  ON a.id_veiculo = v.id_veiculo 
INNER JOIN motoristas    m  ON g.id_motorista = m.id
GROUP BY m.id /*Isto aqui faz a segunda parte da mágica*/