INNER JOIN Group Very slow in consultation

Asked

Viewed 455 times

1

I have a select in mysql that is taking too long to return the records, I noticed that it occurs after the table gets fuller.

  • This select makes 4 INNER JOINS and takes the last record of each id belonging to the id_driver column with a GROUP, the script works perfectly longer is too slow.

There is some way to improve this and make it faster, below follows my script

minha tabela

  $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

  • I imagine the "id" fields have indexes, it would be an obvious problem. Sub-consultations usually give a lot of performance problems, it would be nice to test the performance of the sub-consumption alone, and after the main consultation with a fixed list after the IN, to see if they play alone.

  • http://www.sitepoint.com/using-explain-to-write-better-mysql-queries/

  • The problem is not in your INNER JOIN, considering that these joins are related to Foreign Keys and Primarys. The slowness will occur due to your IN and your Subquery - GROUP BY "should" be used only for functions like SUM, COUNT, etc., avoid its use, try to use ORDER with LIMIT and your MAX already restricts the result to 1 records, then use " = " instead of IN, optimize your WHERE. Paste a EXPLAIN of your query to help where your neck is.

1 answer

2

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*/

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.