Hello! I'm currently doing a summer job and I have to extend an existing program.

My boss asked me to make a tool for our clients so they can see how much their employees cost, per month. But that's not all. The thing is that a company can have one or more 'societies', or subcompanies. We want know how much an employee costs per society in a company.

These are the table I use:

• society: a subcompany with a people_id which contains the name, etc. of the society
• timesheet: timesheet entries that contain person and society information
• people: all people or contacts in the database
• salarystate: contains the salary for a person for a specific month
• overhead: overhead cost for a person for a specific month (note that date is a string (!) formatted like this: YYYY-MM-DD)

This query works, but it takes very long time to execute. Is there a way to make it faster?

I select the year and month, get the name of the employee (worker) and the name of the society. Then I select the sum of the minutes the employee has worked (for a specific society). And finally I calculate the cost by checking his salary for that month and the overhead for that month.

SELECT
    YEAR(TS.assigndate) AS timesheet_year,
    MONTH(TS.assigndate) AS timesheet_month,
    CONCAT(TP.name, ' ', TP.firstname) AS worker,
    CONCAT(SP.name, ' ', SP.firstname) AS society,
    (
        SELECT
            SUM(timeunits) AS minutes
        FROM timesheet
        WHERE
            people_id = TP.id AND
            society_id = S.id AND
            MONTH(assigndate) = timesheet_month
    ) AS minutes,
    (
        SELECT (minutes / 60)
    ) AS hours,
    (
        SELECT(OO.hourtarif + SS.hourtarif) AS cost
        FROM salarystate SS, overhead OO
        WHERE
            people_id = TP.id AND
            YEAR(OO.date) = timesheet_year AND
            MONTH(OO.date) = timesheet_month AND
            CONVERT(SUBSTRING(SS.month FROM 1 FOR 4), UNSIGNED) = timesheet_year AND
            CONVERT(SUBSTRING(SS.month, -2), UNSIGNED) = timesheet_month
    ) AS cost,
    (
        SELECT (hours * cost)
    ) AS total_cost
FROM timesheet TS, society S, people SP, people TP
WHERE
    S.id = TS.society_id AND
    SP.id = S.people_id AND
    TP.id = TS.people_id
GROUP BY timesheet_year, timesheet_month, worker, society; 

Originally asked by: gillis on Stack Overflow