vendredi 31 juillet 2015

to accelerate mysql query

I have this query in mySql. This query is taking too long to run, and I know the problem is the selectors (coalesce ((SELECT ...), I do not know how to speed up a query, via join.

I am hoping some of you SQL gurus will be able to help me.

SELECT
    COALESCE(
        (SELECT CONCAT(d.PRIJEVOZNIK, ' ', d.VOZAC_TRANSFER) 
         FROM dokum_zag as d
         where d.SIFKNJ='NP' and
               d.ID_VEZA=dokum_zag.ID and 
               d.korisnicko_ime=dokum_zag.korisnicko_ime 
         ),'') as PRIJEVOZNIK,
      (RELACIJA_TRANS_VOZ_TRANS) as RELACIJA_TRANS_VOZ, 
      (PRIJEVOZNIK_POVRATNI_TRANS) as PRIJEVOZNIK_POVRATNI, 
      (VAUC_KNJIZENO_TRANS) as VAUC_KNJIZENO, 
      ID_NALOGA, 
      ID_NALOGA_POV, 
      ID_VAUCHER, 
      DOLAZAK, VRIJ_TRANSFER,ODLAZAK,VRIJEME_LETA_POVRAT ,BRDOK, NOSITELJ_REZ, RELACIJA_TRANS, VOZILO_NAZIV, BROJ_NALOGA,BROJ_NAL_POV,BROJ_VAUCHER,BROJ_SOBE,VALIZN,PAX, MPIZN,ID 
FROM
    dokum_zag 
WHERE 
    korisnicko_ime = '10' and 
    ((DOLAZAK='2015-07-30') or (ODLAZAK='2015-07-30')) and 
    STORNO <> 'DA' and 
    SIFKNJ = 'TR' and 
   ((YEAR(DOLAZAK)= '2015') or (YEAR(ODLAZAK)= '2015')) 
order by 
   (CASE WHEN DOLAZAK < '2015-07-30' THEN ODLAZAK ELSE DOLAZAK END) , 
   (CASE WHEN DOLAZAK < '2015-07-30' THEN VRIJEME_LETA_POVRAT ELSE VRIJ_TRANSFER END), ID 

Aucun commentaire:

Enregistrer un commentaire