From: Martin Gainty
Date: August 23 2012 7:27pm
Subject: RE: view query is slow
List-Archive: http://lists.mysql.com/mysql/228068
Message-Id: <BLU142-W21AE6D6F4531EA824256C2AEBE0@phx.gbl>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="_29c4d7ca-0041-4941-94e8-51dabc5cf03d_"

--_29c4d7ca-0041-4941-94e8-51dabc5cf03d_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


If memory serves predicates convert strings to column-data-type (in your ca=
se DATE) this *should* help
WHERE dateexam =3D STR_TO_DATE('2012-08-13'=2C'%Y-%m-%d')
does this help?
Martin=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de confidentialit=
=E9

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut =EAtre privil=E9gi=E9. Si vous n'=EAtes=
 pas le destinataire pr=E9vu=2C nous te demandons avec bont=E9 que pour sat=
isfaire informez l'exp=E9diteur. N'importe quelle diffusion non autoris=E9e=
 ou la copie de ceci est interdite. Ce message sert =E0 l'information seule=
ment et n'aura pas n'importe quel effet l=E9galement obligatoire. =C9tant d=
onn=E9 que les email peuvent facilement =EAtre sujets =E0 la manipulation=
=2C nous ne pouvons accepter aucune responsabilit=E9 pour le contenu fourni=
.


> From: jim@stripped
> Subject: view query is slow
> Date: Thu=2C 23 Aug 2012 11:30:17 -0700
> To: mysql@stripped
>=20
> I am working on a view based on this query:
>=20
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> SELECT
> -- Patient Info
> p.IdPatient=2C
> p.IdLastword MRN=2C=20
> p.NameLast=2C=20
> p.NameFirst=2C=20
> p.Addr1=2C=20
> p.Addr2=2C=20
> p.AddrCity=2C=20
> p.AddrState=2C=20
> p.AddrZip=2C
> p.Gender=2C=20
> p.DateOfBirth=2C
> -- Provider Info
> af.IdAffil=2C
> af.PractName=2C=20
> af.OfficeName=2C
> -- Exam Info
> e.IdExam=2C
> e.dateexam=2C=20
> a.WorkArea dept=2C=20
> a.Room location=2C=20
> e.ProcModeCode=2C
> e.ProcName=2C
> e.IdRefSite=2C
> ec.IdCPT=2C
> e.zzk exam_zzk=2C
> ec.zzk examcpt_zzk
> FROM patient_ p
> LEFT JOIN exams e ON e.IdPatient =3D p.IdPatient
> LEFT JOIN Examcpt_ ec ON (e.IdExam =3D ec.IdExam AND '1' =3D Quantity)
> LEFT JOIN Copy_ c ON CONCAT(e.IdAppt =2C '0') =3D c.IdApptType
> LEFT JOIN Appt_ a ON e.IdAppt =3D a.IdAppt
> LEFT JOIN Affil_ af ON c.IdPractAffil =3D af.IdAffil
> WHERE=20
> p.AddrState =3D 'WA'
> AND e.statusnumber =3D '4'
> AND e.IdRefSite <> 'S50'
> AND e.IdRefSite <> 'S51'=20
> AND e.IdREfSite <> 'S63'=20
> AND p.DateOfBirth < DATE_ADD(CURDATE()=2C INTERVAL '-2' MONTH)=20
> AND a.zzk IS NOT NULL
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>=20
> If I run this query itself (not in the view)=2C and add this:
>=20
> AND e.dateexam =3D '2012-08-13'
>=20
> it runs like lightning=2C super fast. But if I run the query against the =
view=2C for example "SELECT * FROM exam_view WHERE dateexam =3D '2012-08-13=
'
>=20
> It is so glacially slow that I end up having to kill the query. What is g=
oing on=2C and how can I fix this?
>=20
> Jim McNeely
> Northwest Radiologists
> Senior Database Programmer
> 360-788-9022 desk
> 360-303-3332 mobile=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql
>=20
 		 	   		  =

--_29c4d7ca-0041-4941-94e8-51dabc5cf03d_--