Hello everyone,
Kindly ask if anyone could help to change the following SQL query to improve the speed, actually it takes 10-15 seconds to load the results. I am not a developer myself but I can understand the code, what I cannot tell is how to rewrite the query to be more efficient and faster.
Any advise is much appreciated.
SELECT
DATEDIFF(NOW(),i.date_blh) AS dif, i.*, d.id AS invoice_deduction_id, d.deduction_amount,
dep.name AS pi_department, d.paied_amount, d.last_invoice, con.company_name AS vendor, t.pr_rep_num,
t.item_desc, td.mandate, td.grant_no_budget_line, td.contract_link, b.name AS headings,
CONCAT(e1.fname,' ',e1.lname) AS responsible_name, CONCAT(e2.fname,' ',e2.lname) AS requestor_name ,
BLH.employe_id AS bl_holder_id, CONCAT(BLHN.fname,' ',BLHN.lname) AS bl_holder_name, SUBSTR(t.status,3,15) AS pi_status
FROM `invoices` AS i
left join invoice_deductions as d
on d.invoice_id=i.id and (d.deduction_amount!=0 or d.paied_amount!=0)
left join logistics_tracking_data as td
on td.id=d.logistics_tracking_data_id
left join logistics_tracking as t
on t.id=td.logistics_tracking_id
left join fin_budget as b
on b.code=td.grant_no_budget_line and b.mandate=td.mandate
left join hr_employes as e1
on e1.id=i.responsible
left join hr_employes as e2
on e2.id=t.employe_id
left join hr_department as dep
on dep.id=i.hr_department_id
left join logistics_contacts as con
on con.id=i.logistics_contacts_id
left join fin_budget_holders as BLH
on td.grant_no_budget_line = BLH.code and BLH.mandate=td.mandate /*and BLH.sort=10*/
left join hr_employes as BLHN
on BLH.employe_id = BLHN.id
WHERE
i.pi!=0
and i.date_submission_rs!=0
and d.id is not null
and (i.sub_status=3 OR (i.sub_status>3 and DATEDIFF(NOW(),i.date_blh)<5))