Query taking lot of time to execute

Question

I am trying to run a query to get data one time from a client database to our database but a query is taking a lot of time to execute, when I change the order by from primary key user_appoint.id to user_appoint.u_id below is my query

SELECT 
CONCAT('D',user_appoint.`id`) AS ApptId,
user_appoint.`u_id`,
tbl_questions.CandAns,
tbl_questions.ExamAns,
tbl_questions.QueNote,
CONCAT("[",GROUP_CONCAT(CONCAT('"',`tbl_investigations`.`test_id`,'":"',tbl_investigations.`result`,'"')),"]") AS CandInv,
CONCAT("[",GROUP_CONCAT(CONCAT('"',`tbl_investigations`.`test_id`,'":"',tbl_investigations.`comments`,'"')),"]") AS IntComm,
IF(tbl_questions.LastUpdatedDateTime>MAX(tbl_investigations.`ModifiedAt`),tbl_questions.LastUpdatedDateTime,MAX(tbl_investigations.`ModifiedAt`)) AS LastUpdatedDateTime,
CONCAT('D',user_appoint.`id`) AS UniqueId
FROM user_appoint
LEFT JOIN tbl_investigations ON tbl_investigations.`appt_id`=user_appoint.`id` AND tbl_investigations.`ModifiedAt`>'2011-01-01 00:00:00' 
LEFT JOIN tbl_questions   ON tbl_questions.`appt_id` =user_appoint.`id` AND tbl_questions.`LastUpdatedDateTime`>'2011-01-01 00:00:00'
GROUP BY user_appoint.`id` 
HAVING LastUpdatedDateTime>'2011-01-01 00:00:00'
ORDER BY user_appoint.`u_id`
LIMIT 0, 2000;

user_appoint.u_id is properly indexed.

score 0 · Answer 1 · answered Oct 04 '17 at 06:13

Please check the explain plan of your query. And its better to always share explain plan with your original question.

explain format=json

SELECT CONCAT('D',user_appoint.id) AS ApptId, user_appoint.u_id, tbl_questions.CandAns, tbl_questions.ExamAns, tbl_questions.QueNote, CONCAT("[",GROUP_CONCAT(CONCAT('"',tbl_investigations.test_id,'":"',tbl_investigations.result,'"')),"]") AS CandInv, CONCAT("[",GROUP_CONCAT(CONCAT('"',tbl_investigations.test_id,'":"',tbl_investigations.comments,'"')),"]") AS IntComm, IF(tbl_questions.LastUpdatedDateTime>MAX(tbl_investigations.ModifiedAt),tbl_questions.LastUpdatedDateTime,MAX(tbl_investigations.ModifiedAt)) AS LastUpdatedDateTime, CONCAT('D',user_appoint.id) AS UniqueId FROM user_appoint LEFT JOIN tbl_investigations ON tbl_investigations.appt_id=user_appoint.id AND tbl_investigations.ModifiedAt>'2011-01-01 00:00:00' LEFT JOIN tbl_questions ON tbl_questions.appt_id =user_appoint.id AND tbl_questions.LastUpdatedDateTime>'2011-01-01 00:00:00' GROUP BY user_appoint.id HAVING LastUpdatedDateTime>'2011-01-01 00:00:00' ORDER BY user_appoint.u_id LIMIT 0, 2000;

score 0 · Answer 2 · answered Oct 04 '17 at 06:16

On looking at your query,I could see lot of concat,aggregate function and join is being performed in single query.

These operations will be performed for all 2000 records as you have set limit on query execution. This might have caused query to slow down its execution.

Paul Maxwell · Answer 3 · 2017-10-04T07:15:39.343

You have 2 identical columns with different aliases

CONCAT('D',user_appoint.`id`) AS ApptId,
CONCAT('D',user_appoint.`id`) AS UniqueId

(changed) Assuming NULLs may occur in these date columns then comparing the max() values will overcome any adverse impacts by NULL:

if(max(tbl_questions.lastupdateddatetime) > max(tbl_investigations.`modifiedat`) , max(tbl_questions.lastupdateddatetime), max(tbl_investigations.`modifiedat`))  AS LastUpdatedDateTime

Try this:

SELECT *
FROM (
    SELECT
            Concat('D', user_appoint.`id`)  AS ApptId
          , user_appoint.`u_id`
          , tbl_questions.candans
          , tbl_questions.examans
          , tbl_questions.quenote
          , Concat("[", Group_concat(Concat('"', `tbl_investigations`.`test_id`, '":"', tbl_investigations.`result`, '"')), "]") AS CandInv
          , Concat("[", Group_concat(Concat('"', `tbl_investigations`.`test_id`, '":"', tbl_investigations.`comments`, '"')), "]") AS IntComm
          , if(max(tbl_questions.lastupdateddatetime) > max(tbl_investigations.`modifiedat`) , max(tbl_questions.lastupdateddatetime), max(tbl_investigations.`modifiedat`) )  AS LastUpdatedDateTime
          , Concat('D', user_appoint.`id`) AS UniqueId 
    FROM   user_appoint 
           LEFT JOIN tbl_investigations 
                  ON tbl_investigations.`appt_id` = user_appoint.`id` 
                     AND tbl_investigations.`modifiedat` > '2011-01-01 00:00:00' 
           LEFT JOIN tbl_questions 
                  ON tbl_questions.`appt_id` = user_appoint.`id` 
                     AND tbl_questions.`lastupdateddatetime` > '2011-01-01 00:00:00' 
    GROUP  BY user_appoint.`id` 
    HAVING lastupdateddatetime > '2011-01-01 00:00:00' 
    ) d
ORDER  BY `u_id` 
LIMIT  0, 2000
;

HOWEVER

You are using a non-current and non-standard form of GROUP BY clause. MySQL started life allowing this bizarre situation where you could select many columns but only group by one of those. This is completely non-standard for SQL.

In recent versions of MySQL the default settings have changed and using just one column in the GROUP BY clause will cause an error.

So, you may have to change the way you perform the grouping to

GROUP  BY
        user_appoint.`id`
      , user_appoint.`u_id`
      , tbl_questions.candans
      , tbl_questions.examans
      , tbl_questions.quenote

If none of these improve performance please provide the execution plan (as text).

Query taking lot of time to execute

3 Answers3