CodeIgniter Forums
Help with PHP SQL query - Printable Version

+- CodeIgniter Forums (
+-- Forum: Using CodeIgniter (
+--- Forum: General Help (
+--- 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.

DATEDIFF(NOW(),i.date_blh) AS dif, i.*, AS invoice_deduction_id, d.deduction_amount, 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, 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 and (d.deduction_amount!=0 or d.paied_amount!=0)

left join logistics_tracking_data as td

left join logistics_tracking as t

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

left join hr_employes as e2

left join hr_department as dep

left join logistics_contacts as con

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 =


and i.date_submission_rs!=0
and 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


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?