Help with PHP SQL query

Links for php scripts

Moderators: egami, macek, gesf

Post Reply
bbogdann
New php-forum User
New php-forum User
Posts: 1
Joined: Wed Jan 24, 2018 12:53 am

Wed Jan 24, 2018 1:01 am

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

element121
php-forum Fan User
php-forum Fan User
Posts: 622
Joined: Sat Jun 06, 2015 11:46 am
Contact:

Thu Jun 14, 2018 9:42 am

Hi,

Appreciate this is an old post, but first thing to check is if every table has indexes set-up on the columns you are joining on.
Also it's best to specify exact column names you need from the invoices table, so instead of i.* you would have i.my_column_name1 etc....

Regards,
Jon

Post Reply