I really have a very strange problem in my MySQL InnoDB database. I have the following query:
SELECT DISTINCT p.idProject AS idProject, p.name AS name, 0 AS isConfirm FROM Projects p JOIN team_project tp ON (p.idProject = tp.idProject) JOIN projtimes pt ON (p.idProject = pt.idProject) JOIN CalledTimesTbl ctt ON (p.idProject = ctt.idProject) LEFT JOIN NextCalls nc ON (ctt.idCustomer = nc.idCustomer AND ctt.idProject = nc.idProject) WHERE tp.idTeam = 158 AND p.activated = 1 AND current_date >= p.projStart AND current_date < p.confirmStart AND pt.invitesCount < pt.maxPerPresentation AND (nc.idCustomer IS NULL OR nc.nextCall < now()) ORDER BY p.name
Usually the request is executed normally, but sometimes - for example, when I set tp.idTeam = 147 , it works very slowly (for example, 10 or 20 seconds). When I create an alternative command and configure the correct table values to have the same result with a different idTeam value, the query is completed in a split second.
I profiled the request and noticed that when the request is slow, there is one thing that consumes most of the time:
Copying to tmp table | 12.489197
I was a little surprised that the query creates the tmp table, but ok - it creates it every time the query is executed - also when it is fast. I just add that db is well designed, has all the necessary foreign keys, etc.
How to find the source of slow executions and eliminate it?
EDIT: EXPLAIN results:
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE tp ref unique_row,idTeam idTeam 4 const 56 Using temporary; Using filesort 1 SIMPLE p eq_ref PRIMARY,projStart,confirmStart PRIMARY 4 xxx.tp.idProject 1 Using where 1 SIMPLE pt ref uniq_projtimes uniq_projtimes 4 xxx.tp.idProject 1 Using where; Distinct 1 SIMPLE ctt ref idProject idProject 4 xxx.tp.idProject 3966 Using index; Distinct 1 SIMPLE nc eq_ref PRIMARY,idProject PRIMARY 8 xxx.ctt.idCustomer,xxx.tp.idProject 1 Using where; Distinct
EDIT2: EXPLAIN EXTENDED results first for fast query, second for slow.
id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE tp ref unique_row,idTeam idTeam 4 const 1 100 Using temporary 1 SIMPLE p eq_ref PRIMARY,projStart,confirmStart PRIMARY 4 xxx.tp.idProject 1 100 Using where 1 SIMPLE pt ref uniq_projtimes uniq_projtimes 4 xxx.tp.idProject 1 100 Using where; Distinct 1 SIMPLE ctt ref idProject idProject 4 xxx.tp.idProject 46199 100 Using index; Distinct 1 SIMPLE nc eq_ref PRIMARY,idProject PRIMARY 8 xxx.ctt.idCustomer,xxx.tp.idProject 1 100 Using index; Distinct id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE p eq_ref PRIMARY,projStart,confirmStart PRIMARY 4 xxx.ctt.idProject 1 100 Using where 1 SIMPLE pt ref uniq_projtimes uniq_projtimes 4 xxx.ctt.idProject 1 100 Using where; Distinct 1 SIMPLE tp ref unique_row,idTeam unique_row 8 xxx.pt.idProject,const 1 100 Using where; Using index; Distinct 1 SIMPLE nc eq_ref PRIMARY,idProject PRIMARY 8 xxx.ctt.idCustomer,xxx.tp.idProject 1 100 Using index; Distinct