Efficiency of Subselects Within a MySQL Select Query

Question

I am trying to produce a report from a collection of database tables, and I'm finding that the query I've written is super slow. I realise that using multiple subselects on a select query will increase the time the query will take to complete, because each row requires multiple subqueries to the database but what can I do to make my query faster and more efficient?

I've tried wrapping this in a prepared statement and it has little effect, Is there a way that I'm missing that would allow the extraction of the needed information faster?

SELECT Contractor.id,
Contractor.Unique_ID,
Contractor.FName,
Contractor.SName,
Contractor.MIName,
Contractor.NI_Number,
Contractor.Address,
Contractor.Phone_Main,
Contractor.Phone_Mobile,
Contractor.Email,
Contractor.Bank_Name,
Contractor.Bank_Account_Name,
Contractor.Bank_Account_Sort_Code,
Contractor.Bank_Account_Number,
Contractor.DOB,
Contractors.DateTime_Account_Created,
Contractor.Payment_Frequency,
Contractor.Saving_Tax_Pref,
Contractor.Holiday_Savings_Pref,
Contractor.UTR,
Contractor.DateTime_Last_Logged_In,
Contractor.Leaving_Date,
Agency.Name,
(
    SELECT ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2)
    FROM ContractorSavings
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id
    WHERE fullyPaid = 0
    AND ContractorSavings.type_id = 2
    AND SavingsToPayJunction.ContractorId = Contractor.id
) AS 'Tax_Saving_Balance',
(
    SELECT ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2)
    FROM ContractorSavings
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id
    WHERE fullyPaid = 0
    AND ContractorSavings.type_id = 2
    AND SavingsToPayJunction.ContractorId = Contractor.id
) AS 'Holiday_Saving_Balance',
(
    SELECT ROUND(SUM(Expense_Value),2)
    FROM Contractor_Expenses
    WHERE Contractor_ID = Contractor.id
    AND authorised = 0
)AS 'Expenses_Claimed',
(
    SELECT ROUND(SUM(Expense_Value - amountAllocated),2)
    FROM Contractor_Expenses
    WHERE Contractor_ID = Contractor.id
    AND authorised = 1
    AND fullyAllocated = 0
)AS 'Expenses_Authorised',
(
    SELECT MAX(Contractor_Pay.Date_Earned)
    FROM Contractor_Pay
    WHERE Contractor_Pay.Contractor_ID = Contractor.Unique_ID
)AS 'Last_Pay_Date'
FROM Contractor
LEFT JOIN Agency ON Agency.id = Contractor.Agency_ID

Apologies for the unwieldy query dump. I look forward to your responses.

Thank you,

* POST ANSWER EDIT *

For those that care about timings and the like, the answer reduced my query time down from in excess of 3:00 minutes down to 45 seconds.

Answer 1

You can have each of those as subselects in the FROM clause (with GROUP BY Contractor_ID) then JOIN them up:

SELECT
    Contractor.id,
    Contractor.Unique_ID,
    Contractor.FName,
    Contractor.SName,
    Contractor.MIName,
    Contractor.NI_Number,
    Contractor.Address,
    Contractor.Phone_Main,
    Contractor.Phone_Mobile,
    Contractor.Email,
    Contractor.Bank_Name,
    Contractor.Bank_Account_Name,
    Contractor.Bank_Account_Sort_Code,
    Contractor.Bank_Account_Number,
    Contractor.DOB,
    Contractors.DateTime_Account_Created,
    Contractor.Payment_Frequency,
    Contractor.Saving_Tax_Pref,
    Contractor.Holiday_Savings_Pref,
    Contractor.UTR,
    Contractor.DateTime_Last_Logged_In,
    Contractor.Leaving_Date,
    Agency.Name,
    Tax_Saving_Balance.val as Tax_Saving_Balance,
    Holiday_Saving_Balance.val as Holiday_Saving_Balance,
    Expenses_Claimed.val as Expenses_Claimed,
    Expenses_Authorised.val as Expenses_Authorised,
    Last_Pay_Date.val as Last_Pay_Date
FROM Contractor
LEFT JOIN Agency ON Agency.id = Contractor.Agency_ID
LEFT JOIN (
    SELECT SavingsToPayJunction.ContractorId, ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2) as val,
    FROM ContractorSavings
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id
    WHERE fullyPaid = 0
    AND ContractorSavings.type_id = 2
    GROUP BY SavingsToPayJunction.ContractorId
) as Tax_Saving_Balance ON Tax_Saving_Balance.ContractorId = Contractor.id
LEFT JOIN (
    SELECT SavingsToPayJunction.ContractorId, ROUND(SUM(ContractorSavings.value - ContractorSavings.paidOutValue), 2) as val
    FROM ContractorSavings
    LEFT JOIN SavingsToPayJunction ON SavingsToPayJunction.SavingsId = ContractorSavings.id
    WHERE fullyPaid = 0
    AND ContractorSavings.type_id = 2
    GROUP BY SavingsToPayJunction.ContractorId
) as Holiday_Saving_Balance ON Holiday_Saving_Balance.ContractorId = Contractor.id
LEFT JOIN (
    SELECT Contractor_ID, ROUND(SUM(Expense_Value),2) as val
    FROM Contractor_Expenses
    WHERE authorised = 0
    GROUP BY Contractor_ID
) as Expenses_Claimed ON Expenses_Claimed.Contractor_ID = Contractor.id
LEFT JOIN (
    SELECT Contractor_ID, ROUND(SUM(Expense_Value - amountAllocated),2) as val
    FROM Contractor_Expenses
    WHERE authorised = 1
    AND fullyAllocated = 0
    GROUP BY Contractor_ID
) as Expenses_Authorised ON Expenses_Authorised.Contractor_ID = Contractor.id
LEFT JOIN (
    SELECT Contractor_ID, MAX(Contractor_Pay.Date_Earned)
    FROM Contractor_Pay
    GROUP BY Contractor_ID
) as Last_Pay_Date ON Last_Pay_Date.Contractor_ID = Contractor.Unique_ID