From: James W. McNeely
Date: November 14 2012 10:33pm
Subject: query tuning
List-Archive: http://lists.mysql.com/mysql/228625
Message-Id: <EC33E07D-D53A-4BBC-B851-67885FFAE5DD@nwrads.com>
MIME-Version: 1.0 (Apple Message framework v1283)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

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=20
-- JOIN Appt_ a ON e.IdAppt =3D a.IdAppt=20
-- JOIN Affil_ af ON a.IdAffil_primary =3D af.IdAffil=20
WHERE e.dateexam >=3D '2012-09-01' AND e.dateexam <=3D '2012-09-30'=20
AND e.ProcModeCode =3D 'P'=20

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=20
FROM exams e=20
JOIN Appt_ a ON e.IdAppt =3D a.IdAppt=20
JOIN Affil_ af ON a.IdAffil_primary =3D af.IdAffil=20
WHERE e.dateexam >=3D '2012-09-01' AND e.dateexam <=3D '2012-09-30'=20
AND af.IdPract =3D 'D00400'=20

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

SELECT e.zzk=20
FROM exams e=20
JOIN Appt_ a ON e.IdAppt =3D a.IdAppt=20
JOIN Affil_ af ON a.IdAffil_primary =3D af.IdAffil=20
WHERE e.dateexam >=3D '2012-09-01' AND e.dateexam <=3D '2012-09-30' AND =
af.IdPract =3D 'D00400' AND e.ProcModeCode =3D 'L'=20
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=