List:General Discussion« Previous MessageNext Message »
From:James W. McNeely Date:November 14 2012 10:33pm
Subject:query tuning
View as plain text  
I have a query I'm having trouble with. If do this query which is DATE plus ProcModecode,
it is very fast:

SELECT e.zzk FROM exams e 
-- JOIN Appt_ a ON e.IdAppt = a.IdAppt 
-- JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil 
WHERE e.dateexam >= '2012-09-01' AND e.dateexam <= '2012-09-30' 
AND e.ProcModeCode = 'P' 

Notice that the joins are commented out.

If I do this query, which doesn't have the ProcModeCode, but does have the IdPract across
the join, it is also fast, but not as fast:

SELECT e.zzk 
FROM exams e 
JOIN Appt_ a ON e.IdAppt = a.IdAppt 
JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil 
WHERE e.dateexam >= '2012-09-01' AND e.dateexam <= '2012-09-30' 
AND af.IdPract = 'D00400' 

BUT, if I do this, with the ProcModeCode AND the IdPract, it is so slow I have to kill the
query:

SELECT e.zzk 
FROM exams e 
JOIN Appt_ a ON e.IdAppt = a.IdAppt 
JOIN Affil_ af ON a.IdAffil_primary = af.IdAffil 
WHERE e.dateexam >= '2012-09-01' AND e.dateexam <= '2012-09-30' AND af.IdPract =
'D00400' AND e.ProcModeCode = 'L' 
ORDER BY e.zzk DESC LIMIT 30

Here is the result of an explain on this:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	e	index	dateexam,IdAppt,ProcModeCode	PRIMARY	4	NULL	1121043	"Using where"
1	SIMPLE	af	ref	IdAffil,IdPract	IdPract	51	const	1	"Using where"
1	SIMPLE	a	ref	IdAppt	IdAppt	99	RIS_archive.e.IdAppt	1	"Using where"

Any ideas about how I can fix this?

TIA

Jim McNeely
Thread
query tuningJames W. McNeely14 Nov
  • RE: query tuningRick James14 Nov
    • Re: query tuningJames W. McNeely14 Nov
      • RE: query tuningRick James15 Nov
        • Re: query tuningJames W. McNeely15 Nov
        • RE: query tuningIƱigo Medina15 Nov