Veritabanı sorgusu daha fazla nasıl hızlandırılır?

Sorguyu hızlandırmak için önerisi olanlar bildiklerini paylaşabilir mi ?

SELECT
alim_bilgileri.*,
alim_bilgileri. İd as aid,
expertiz.*,
expertiz. İd as eid,
stok.*,
(SELECT COUNT(loger. İd) FROM loger WHERE arac_id = stok. İd and IFNULL(goruldu, 0) = 0 ) ykga_adet,
(SELECT SUM(masraf. Tutar) FROM masraf WHERE arac_id = stok. İd and tip='GELİR') gelir_tutar,
(SELECT SUM(masraf. Kdv) FROM masraf WHERE arac_id = stok. İd and tip='GELİR') gelir_kdv,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GELİR') gelir_toplam,
(SELECT SUM(masraf. Tutar) FROM masraf WHERE arac_id = stok. İd and tip='GİDER') gider_tutar,
(SELECT SUM(masraf. Kdv) FROM masraf WHERE arac_id = stok. İd and tip='GİDER') gider_kdv,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER') gider_toplam,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and gider_tipi = ('FİNANSMAN')) finansal,
(SELECT COUNT(masraf. İd) FROM masraf WHERE arac_id = stok. İd and IFNULL(pk, 0) = 0 and tip IN ('GİDER','GELİR') and sanal != 1) kontrol_onay,
(SELECT COUNT(masraf. İd) FROM masraf WHERE arac_id = stok. İd and IFNULL(pk, 0) = 0 and tip IN ('GİDER','GELİR') and sanal = 1) sanal_kontrol_onay,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and gider_tipi IN ('TRAFİK SİGORTASI','Trafik Zeyil')) trafik,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and gider_tipi LIKE ('MTV%')) mtv,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and sanal = 1 and gider_tipi!='BANKA KATKISI') sanal,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and sanal = 1 and gider_tipi='BANKA KATKISI') banka,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and gider_tipi='İLAVE AKSESUAR') ilave_aksesuar,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and masraf_grup='30 ATÖLYE MSRF') atolye_masraf,
(SELECT SUM(masraf. Toplam) FROM masraf WHERE arac_id = stok. İd and tip='GELİR' and masraf_grup='30 ATÖLYE MSRF') atolye_masraf_gelir,

(SELECT COUNT(id) FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and gider_tipi LIKE ('MTV%')) mtv_adet,
(SELECT gider_tarihi FROM masraf WHERE arac_id = stok. İd and tip='GİDER' and gider_tipi LIKE ('MTV%') order by str_to_date(gider_tarihi,'%d-%m-%Y') desc limit 0,1) mtv_tarih,
(SELECT satis_fiyat FROM satis_fiyat WHERE arac_id = stok. İd ORDER BY id desc LIMIT 0,1 ) as satis_fiyati,
(SELECT satis_toplam FROM satis_fiyat WHERE arac_id = stok. İd ORDER BY id desc LIMIT 0,1 ) as satis_toplam_kdvli,
(SELECT satis_kdv FROM satis_fiyat WHERE arac_id = stok. İd ORDER BY id desc LIMIT 0,1 ) as satis_kdvsi,
(SELECT liste_toplam FROM satis_fiyat WHERE arac_id = stok. İd ORDER BY id desc LIMIT 0,1 ) as liste_toplam,
(SELECT mtv_tutar FROM satis_fiyat WHERE arac_id = stok. İd ORDER BY id desc LIMIT 0,1 ) as mtv_tutar,
(SELECT tescil_tutar FROM satis_fiyat WHERE arac_id = stok. İd ORDER BY id desc LIMIT 0,1 ) as tescil_tutar,
(SELECT otv_tutar FROM satis_fiyat WHERE arac_id = stok. İd ORDER BY id desc LIMIT 0,1 ) as otv_tutar,

(SELECT COUNT(id) FROM atolye WHERE arac_id = stok. İd) isemri,
(SELECT SUM(ongorulen) FROM atolye WHERE arac_id = stok. İd) as ongorulen,
(SELECT SUM(ongorulen) FROM atolye WHERE arac_id = stok. İd and yonetici_onay = 1) as onaylanan,
(SELECT SUM(gerceklesen) FROM atolye WHERE arac_id = stok. İd) as gerceklesen
FROM stok
LEFT JOIN alim_bilgileri ON alim_bilgileri. Arac_id = stok. İd
LEFT JOIN expertiz ON expertiz. Arac_id = stok. İd
WHERE durum IN ('STOKTA','ONAY_BEKLEYEN','TEKLIF_ONAY') and alim_tipi != 'KONSINYE'

S
0 kişi takip ediyor.
Misafir olarak yayınla
19
19 CEVAP

herşeyi gidip tek tek almak yerine toplu şekilde alıp case when ve union ile toparlayabilirsin.

S

ufak bir örnek verebilirmisin.

Serkan Gumus 6 yıl

çok vaktim yok bayağı uzun bir query kısa kısa yazayım devamını toparlayabilirsin belki: select *,......, SUM(case whena. Yonetici_onay = 1 THENa. Ongorulen ELSE 0 END) ongorulen_toplam from stok inner join atolye aa. X = stokid....... inner join satis_fiyatb. Y = stokid

Semih Doğangüzel 6 yıl