Welcome Guest, Not a member yet? Register   Sign In
Help with PHP SQL query
#1

(This post was last modified: 07-05-2020, 09:33 AM by jrjong.)

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 aol login 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))
============================================================================================================
Reply
#2

@jrjong,

Wow!! To do what you are asking we are going to need more details such as the db structures for all tables involved. Also, have you run the SQL explain statement to see what recommendations it suggests to you. I'm assuming that you are using MySQL. Also, what version of MySQL are you using?
Reply




Theme © iAndrew 2016 - Forum software by © MyBB