Help with SQL query

database
sql
php

#1

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))

#2

Hi @manea_d_bogdan welcome to the forums.

[off-topic]
When you post code in the forum, you need to format it. To do so you can either select all the code and click the </> button, or type 3 backticks ``` on a separate line both before and after the code block.
[/off-topic]


#3

Thank you. Will do so.


#4

The first thing to do were to check if you have set the necessary indices.


#5

first join should be INNER not LEFT OUTER, and then you can drop and d.id is not null from the WHERE clause

but yeah, improving this will depend in indexes


#6

Thank you for advise.


closed #7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.