Help with PHP SQL query - Printable Version +- 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? |