Copying to tmp table slow for query ... but only sometimes

Question

I have a query, and I admit it may not be the prettiest query in the world. Sometimes this query runs fairly fast (e.g. 200ms) and other times it runs super slow (e.g. 30s). I don't know why, and really need some help figuring this out.

Here's the query:

select SQL_CALC_FOUND_ROWS distinct study.id from study
join company on study.company_id = company.id
left join studystudytype on studystudytype.study_id = study.id
left join studytype on studytype.id = studystudytype.studytype_id
left join patient on patient.id = study.patient_id
left join location on location.id = study.location_id
left join user as referring on referring.id = study.referrer_id
left join user as ordering on ordering.id = study.ordering_id
left join user as tech on tech.id = study.tech_id
where study.status not in ('skip', 'merge', 'tmp')
and study.stat = '0'
and study.company_id in (338)
and ((
study.company_id = '338') or (study.id in(select distinct study_id from studyassign where (object_type = 'Group' and object_id in(select grp_id from grpuser where user_id in (45088))) or (object_type = 'User' and object_id in (45088)))
)) and study.status in ('unread','merging') and study.deleted = '0' order by study.study_date asc, study.ctime asc limit 16 offset 0;

And here's its EXPLAIN:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: company
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: Using index; Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: study
         type: ref
possible_keys: company_id,study_status
          key: company_id
      key_len: 4
          ref: const
         rows: 14088
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: studystudytype
         type: ref
possible_keys: studystudytype_study_id_studytype_id_idx
          key: studystudytype_study_id_studytype_id_idx
      key_len: 4
          ref: echo.study.id
         rows: 1
        Extra: Using index; Distinct
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: studytype
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: echo.studystudytype.studytype_id
         rows: 1
        Extra: Using index; Distinct
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: patient
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: echo.study.patient_id
         rows: 1
        Extra: Using index; Distinct
*************************** 6. row ***************************
           id: 1
  select_type: PRIMARY
        table: location
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: echo.study.location_id
         rows: 1
        Extra: Using index; Distinct
*************************** 7. row ***************************
           id: 1
  select_type: PRIMARY
        table: referring
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: echo.study.referrer_id
         rows: 1
        Extra: Using index; Distinct
*************************** 8. row ***************************
           id: 1
  select_type: PRIMARY
        table: ordering
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: echo.study.ordering_id
         rows: 1
        Extra: Using index; Distinct
*************************** 9. row ***************************
           id: 1
  select_type: PRIMARY
        table: tech
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: echo.study.tech_id
         rows: 1
        Extra: Using index; Distinct
*************************** 10. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: studyassign
         type: index_subquery
possible_keys: studyassign_study_id_object_type_object_id_idx,studyassign_study_id,studyassign_object_type_object_id
          key: studyassign_study_id_object_type_object_id_idx
      key_len: 4
          ref: func
         rows: 1
        Extra: Using index; Using where
*************************** 11. row ***************************
           id: 3
  select_type: DEPENDENT SUBQUERY
        table: grpuser
         type: unique_subquery
possible_keys: grpuser_grp_id_user_id_idx,user_id
          key: grpuser_grp_id_user_id_idx
      key_len: 8
          ref: func,const
         rows: 1
        Extra: Using index; Using where
11 rows in set (0.00 sec)

Here's the execution profile under heavy load. Notice how much time is spent in copying to temp table. (It's 5.876, 3.119, and 24.069, if you don't want to click.)

I've thought a lot of about changing tmp_table_size and max_heap_table_size, but ... with the identical result set sometimes coming back in 200ms, I don't know why those values would cause and/or fix the slowness.

For instance, here is the profile of the same query, run just a few minutes later.

On a hunch, I temporarily reduce the size of tmp_table_size, and ran the query. The steps in the profile explicitly show writing the temp table to disk, and these weren't show earlier.

So, I think that:

1. The problem isn't with the query itself (in isolation), because usually the query runs quite fast.
2. There's enough memory allocated for the temp tables, and MySQL isn't writing the temp table to disk, because when I artificially lower tmp_table_size, the contents of the profile change.
3. I could change the query some (e.g. by removing DISTINCT) and remove the need for temp tables. But that doesn't explain why the performance of the temp tables change so much.

Do you have any ideas?

Answer 1

Maybe my answer is too late but i have this problem when use Mariadb. I have restart db service 3 ~5 times per day and still stress about that. After delete join_buffer_size in my config file today i dont need restart service anymore. I know it when i read this question: Copying to tmp table taking forever