title: Need to optimize query
body: <p>I have problems running the following query in a shared hosting server. The execution time is set to 10 secs but my query takes long and I cannot change it.</p>
  
  <pre><code>SELECT M.fecha_mov, -SUM(M.monto) monto, -SUM(M.interes) interes, -SUM(M.iva) iva, -SUM(M.capital) capital, 
  B.banco, 
  COALESCE(tg.nombre, tp.NombComp) nombreGrupo, CONCAT(nombres,' ',apellido_paterno,' ',apellido_materno) nombre, 
  C.expediente, C.id_credito 
  FROM movimientos M 
  JOIN acreditados A ON A.id_acreditado=M.id_acreditado 
  JOIN personas P ON P.id_persona=A.id_persona 
  JOIN creditos C ON C.id_credito=A.id_credito 
  JOIN abonos AB ON AB.id_movimiento=M.id_movimiento 
  JOIN depositos_bancarios D ON D.id_deposito=AB.id_deposito 
  JOIN cuentas_bancarias B ON B.id_banco=D.id_banco 
  LEFT OUTER JOIN (SELECT R.id_credito, AG.nombre FROM representantes R JOIN agrupaciones AG USING (id_grupo)) tg ON tg.id_credito = C.id_credito 
  LEFT OUTER JOIN (SELECT id_persona, CONCAT(nombres,' ',apellido_paterno,' ',apellido_materno) AS NombComp FROM personas) tp ON A.id_persona = tp.id_persona  
  GROUP BY YEAR(M.fecha_mov), MONTH(M.fecha_mov), P.id_persona 
  HAVING -SUM(M.monto) &gt; 15000 
  ORDER BY B.id_banco, M.fecha_mov, C.id_credito
  </code></pre>
  
  <p>I think both left outer joins are causing the problem without them, the query runs neat. I'm using the left outer join part because just some instances of <strong>representantes</strong> table have a relation with <strong>creditos</strong> table, so in the case there is no relation I use the join with <strong>personas</strong> table.</p>
  
  <p>Do you think this query can be optimized?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Thanks to @simon at rcl and @Kickstart the query has been improved removing one LEFT OUTER JOIN that was not needed and removing the subquery</p>
  
  <pre><code>SELECT M.fecha_mov, -SUM(M.monto) monto, -SUM(M.interes) interes, -SUM(M.iva) iva, -SUM(M.capital) capital, 
  B.banco, 
  COALESCE(AG.nombre, CONCAT(nombres,' ',apellido_paterno,' ',apellido_materno)) nombreGrupo, 
  CONCAT(nombres,' ',apellido_paterno,' ',apellido_materno) nombre, 
  C.expediente, C.id_credito 
  FROM movimientos M 
  JOIN acreditados A ON A.id_acreditado=M.id_acreditado 
  JOIN personas P ON P.id_persona=A.id_persona 
  JOIN creditos C ON C.id_credito=A.id_credito 
  JOIN abonos AB ON AB.id_movimiento=M.id_movimiento 
  JOIN depositos_bancarios D ON D.id_deposito=AB.id_deposito 
  JOIN cuentas_bancarias B ON B.id_banco=D.id_banco 
  LEFT OUTER JOIN representantes R ON R.id_credito=C.id_credito
  LEFT OUTER JOIN agrupaciones AG ON AG.id_grupo=R.id_grupo 
  GROUP BY YEAR(M.fecha_mov), MONTH(M.fecha_mov), P.id_persona 
  HAVING -SUM(M.monto) &gt; 15000 
  ORDER BY B.id_banco, M.fecha_mov, C.id_credito
  </code></pre>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Thank you all for your valuable tips, now I can run my query within 1 sec! I'll post my query plan as suggested, if you have any suggestion please let me know. To be honest I'm new in query plan thing.</p>
  
  <pre>
  '1', 'SIMPLE', 'C', 'ALL', 'PRIMARY', NULL, NULL, NULL, '888', 'Using temporary; Using filesort'
  '1', 'SIMPLE', 'R', 'ref', 'fk_representantes_creditos_idx', 'fk_representantes_creditos_idx', '4', 'creabien_sacredi_dev.C.id_credito', '1', ''
  '1', 'SIMPLE', 'AG', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'creabien_sacredi_dev.R.id_grupo', '1', ''
  '1', 'SIMPLE', 'A', 'ref', 'PRIMARY,FK_acreditados_personas,FK_acreditados_creditos', 'FK_acreditados_creditos', '4', 'creabien_sacredi_dev.C.id_credito', '2', ''
  '1', 'SIMPLE', 'P', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'creabien_sacredi_dev.A.id_persona', '1', ''
  '1', 'SIMPLE', 'M', 'ref', 'PRIMARY,FK_movimientos_acreditados', 'FK_movimientos_acreditados', '4', 'creabien_sacredi_dev.A.id_acreditado', '10', ''
  '1', 'SIMPLE', 'AB', 'ref', 'FK_abonos_depositos_bancarios,FK_abonos_movimientos', 'FK_abonos_movimientos', '8', 'creabien_sacredi_dev.M.id_movimiento', '1', ''
  '1', 'SIMPLE', 'D', 'eq_ref', 'PRIMARY,FK_depositos_bancarios_cuentas_bancarias', 'PRIMARY', '8', 'creabien_sacredi_dev.AB.id_deposito', '1', ''
  '1', 'SIMPLE', 'B', 'ALL', 'PRIMARY', NULL, NULL, NULL, '6', 'Using where; Using join buffer'
  </pre>