From: Rick James
Date: November 14 2012 11:26pm
Subject: RE: query tuning
List-Archive: http://lists.mysql.com/mysql/228628
Message-Id: <2E7DD7ADE53B044C8C8BCD9C5829E1EB14911AA8AB@SP2-EX07VS01.ds.corp.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Can't help without the SHOW CREATE TABLEs.

Perhaps e needs one of these:
INDEX(zzk)
INDEX(ProcModeCode, dateexam)  -- in that order
(I can't predict which index it would use.)

Are IdAppt the same datatype and collation in each table?

> -----Original Message-----
> From: James W. McNeely [mailto:jmcneely@stripped]
> Sent: Wednesday, November 14, 2012 2:34 PM
> To: mysql@stripped
> Subject: query tuning
>=20
> I have a query I'm having trouble with. If do this query which is DATE pl=
us
> ProcModecode, it is very fast:
>=20
> SELECT e.zzk FROM exams e
> -- JOIN Appt_ a ON e.IdAppt =3D a.IdAppt
> -- JOIN Affil_ af ON a.IdAffil_primary =3D af.IdAffil WHERE e.dateexam >=
=3D
> '2012-09-01' AND e.dateexam <=3D '2012-09-30'
> AND e.ProcModeCode =3D 'P'
>=20
> Notice that the joins are commented out.
>=20
> If I do this query, which doesn't have the ProcModeCode, but does have th=
e
> IdPract across the join, it is also fast, but not as fast:
>=20
> SELECT e.zzk
> FROM exams e
> JOIN Appt_ a ON e.IdAppt =3D a.IdAppt
> JOIN Affil_ af ON a.IdAffil_primary =3D af.IdAffil WHERE e.dateexam >=3D =
'2012-
> 09-01' AND e.dateexam <=3D '2012-09-30'
> 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:
>=20
> SELECT e.zzk
> FROM exams e
> JOIN Appt_ a ON e.IdAppt =3D a.IdAppt
> JOIN Affil_ af ON a.IdAffil_primary =3D af.IdAffil WHERE e.dateexam >=3D =
'2012-
> 09-01' AND e.dateexam <=3D '2012-09-30' AND af.IdPract =3D 'D00400' AND
> e.ProcModeCode =3D 'L'
> ORDER BY e.zzk DESC LIMIT 30
>=20
> Here is the result of an explain on this:
>=20
> 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"
>=20
> Any ideas about how I can fix this?
>=20
> TIA
>=20
> Jim McNeely
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql