How to increase performance of complicated MySQL select query?

Question

I am working on project management tool and while generating reports through admin end the loading time for the data from the database is very much > 5 minutes. There are few points which I know would help me to increase the performance but right now I need to have the help in SELECT query

SELECT timesheet_client.organisation as client_name, timesheet_project.title as project_name,
  timesheet_task.name as task, CONCAT(timesheet_user.first_name, ' ', timesheet_user.last_name) as resource_name,
  timesheet_user.bill_factor, timesheet_client.client_type, sum(spent) as spent, sum(delivered_hours) as delivered_hours, 
  sum(billable_hours) as billable_hours, comments, color, lock_color, updated_by, updated_by_date, timesheet_user_psdb.grp_id, 
  timesheet_user_psdb.client_id, timesheet_user_psdb.proj_id, timesheet_user_psdb.task_id, timesheet_user_psdb.uid, 
  timesheet_user_grp.grp_name
FROM timesheet_user_psdb,timesheet_user, timesheet_client, 
  timesheet_project,timesheet_task,timesheet_user_grp
WHERE timesheet_user.username=timesheet_user_psdb.uid and
  timesheet_client.client_id=timesheet_user_psdb.client_id and timesheet_project.proj_id=timesheet_user_psdb.proj_id and
  timesheet_task.task_id = timesheet_user_psdb.task_id and timesheet_user_grp.grp_id=timesheet_user_psdb.grp_id and month =3
  AND year = 2017 and month!='' and timesheet_user_psdb.client_id=326  
GROUP BY timesheet_user_psdb.task_id,timesheet_user_psdb.uid
ORDER BY timesheet_client.client_type desc,timesheet_client.organisation,timesheet_user_psdb.proj_id,
  timesheet_user_psdb.task_id,timesheet_user.uid,timesheet_user_psdb.task_id;

I have already used an index on all the primary keys.

EXPLAIN Output:

Help for this would be highly appreciable.

Is the order by mandatory in this case ? Removing the order clause could help. — Eduard Uta, Apr 07 '17 at 13:00
You should provide table definition, output of `EXPLAIN`, which engine you're using, any engine specific configuration values and how many records you're receiving. No one can run this query and tell you what's going on. My assumption would be that you're I/O bound because you're running default MySQL settings. — Mjh, Apr 07 '17 at 13:29
"I have already used an index on all the primary keys." <-- Indexing the foreign keys is also important! — Johan, Apr 07 '17 at 13:43
@EduardUta: Yes OrderBy is mandatory in this case. I am having different clients, projects, tasks, and users who are working on these tasks. In the report, I am clubbing results using this so it is mandatory for now. — Yogesh Chauhan, Apr 10 '17 at 07:29
@Mjh: Added Explain output in the main Question. Kindly check. — Yogesh Chauhan, Apr 10 '17 at 07:46
What exactly do you mean by "complicated" query? That you are obfuscating your query by using a join syntax that has been out-dated for 25 years? Or that you make the query hard to read by not using table aliases? Or that you make your query hard to understand by not qualifying all columns (spent, comments, color, year, month, ...)? — Thorsten Kettner, Apr 10 '17 at 09:21
You are grouping by task_id and uid. Is this a unique key for timesheet_user_psdb? — Thorsten Kettner, Apr 10 '17 at 09:21
Please tell us what tables these columns reside in: spent, delivered_hours, billable_hours, comments, color, lock_color, updated_by, updated_by_date, month, year. — Thorsten Kettner, Apr 10 '17 at 14:36
If task_id and uid are unique for timesheet_user_psdb and you join the psdb's one user, one client, one project, one task, and one user group, you get just one result row per timesheet_user_psdb. So why are you grouping by task_id and uid at all? What is there to sum up? — Thorsten Kettner, Apr 10 '17 at 14:42

score 1 · Answer 1 · edited May 23 '17 at 12:18

Doing the EXPLAIN should shed some light on it.

You might see a performance gain by doing the table joins explicitly in the FROM clause instead of inside the WHERE (https://dev.mysql.com/doc/refman/5.7/en/join.html). By doing explicit joins (e.g. LEFT OUTER, etc...) you will not only improve the readability of the query, but may be able to use less expensive joins where needed. This also affects how the query is executed as each clause is executed in a specific order (MySQL query / clause execution order).

score 1 · Accepted Answer · answered Apr 10 '17 at 13:39

Give this a try:

SELECT 
    TC.ORGANISATION AS CLIENT_NAME, 
    TP.TITLE AS PROJECT_NAME,
    TT.NAME AS TASK, 
    CONCAT(TU.FIRST_NAME, ' ', TU.LAST_NAME) AS RESOURCE_NAME,
    TU.BILL_FACTOR, 
    TC.CLIENT_TYPE, SUM(SPENT) AS SPENT, -- You should specify which table this comes from
    SUM(DELIVERED_HOURS) AS DELIVERED_HOURS, -- You should specify which table this comes from
    SUM(BILLABLE_HOURS) AS BILLABLE_HOURS, -- You should specify which table this comes from
    COMMENTS, -- You should specify which table this comes from
    COLOR, -- You should specify which table this comes from
    LOCK_COLOR, -- You should specify which table this comes from
    UPDATED_BY, -- You should specify which table this comes from
    UPDATED_BY_DATE, -- You should specify which table this comes from
    TUP.GRP_ID, 
    TUP.CLIENT_ID, 
    TUP.PROJ_ID, 
    TUP.TASK_ID, 
    TUP.UID, 
    TUG.GRP_NAME
FROM    
    TIMESHEET_USER AS TU
        LEFT OUTER JOIN 
    TIMESHEET_USER_PSDB AS TUP
        ON TU.USERNAME = TUP.UID
        LEFT OUTER JOIN
    TIMESHEET_USER_GRP AS TUG
        ON TUP.GRP_ID = TUG.GRP_ID
        LEFT OUTER JOIN     
    TIMESHEET_CLIENT AS TC
        ON TUP.CLIENT_ID = TC.CLIENT_ID
        LEFT OUTER JOIN
    TIMESHEET_PROJECT AS TP
        ON TUP.PROJ_ID = TP.PROJ_ID
        LEFT OUTER JOIN
    TIMESHEET_TASK TT
        ON TUP.TASK_ID = TT.TASK_ID 
WHERE 
    MONTH = 3 AND -- You should specify which table this comes from
    YEAR = 2017 AND -- You should specify which table this comes from
    MONTH != '' AND -- You should specify which table this comes from
    TUP.CLIENT_ID = 326  
GROUP BY 
    TUP.TASK_ID,
    TUP.UID
ORDER BY 
    TC.CLIENT_TYPE DESC,
    TC.ORGANISATION,
    TUP.PROJ_ID,
    TUP.TASK_ID,
    TU.UID,
    TUP.TASK_ID;

Yes, this is working absolutely fine. I have executed this query and it took around 0.0309 seconds to show the results. — Yogesh Chauhan, Apr 11 '17 at 11:40

How to increase performance of complicated MySQL select query?

2 Answers2