+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Help with PHP SQL query (/showthread.php?tid=76854)
Help with PHP SQL query - jrjong - 06-27-2020
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))
============================================================================================================
RE: Help with PHP SQL query - php_rocs - 06-27-2020
@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?