From: James W. McNeely Date: August 23 2012 9:31pm Subject: Re: view query is slow List-Archive: http://lists.mysql.com/mysql/228075 Message-Id: MIME-Version: 1.0 (Apple Message framework v1278) Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable I think I'll just make a nightly process run that drops and then = recreates the table, unless someone has a workable idea of how to make = this view query-able. Thanks! Jim McNeely=20 On Aug 23, 2012, at 2:06 PM, James W. McNeely wrote: > This didn't help, but good try! >=20 > Jim McNeely=20 >=20 > On Aug 23, 2012, at 12:27 PM, Martin Gainty wrote: >=20 >>=20 >> If memory serves predicates convert strings to column-data-type (in = your case DATE) this *should* help >> WHERE dateexam =3D STR_TO_DATE('2012-08-13','%Y-%m-%d') >> does this help? >> Martin=20 >> ______________________________________________=20 >> Verzicht und Vertraulichkeitanmerkung/Note de d=E9ni et de = confidentialit=E9 >>=20 >> Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene = Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede = unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. = Diese Nachricht dient lediglich dem Austausch von Informationen und = entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten = Manipulierbarkeit von E-Mails koennen wir 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, nous te demandons avec bont=E9 que = pour satisfaire informez l'exp=E9diteur. N'importe quelle diffusion non = autoris=E9e ou la copie de ceci est interdite. Ce message sert =E0 = l'information seulement et n'aura pas n'importe quel effet l=E9galement = obligatoire. =C9tant donn=E9 que les email peuvent facilement =EAtre = sujets =E0 la manipulation, nous ne pouvons accepter aucune = responsabilit=E9 pour le contenu fourni. >>=20 >>=20 >>> From: jim@stripped >>> Subject: view query is slow >>> Date: Thu, 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, >>> p.IdLastword MRN,=20 >>> p.NameLast,=20 >>> p.NameFirst,=20 >>> p.Addr1,=20 >>> p.Addr2,=20 >>> p.AddrCity,=20 >>> p.AddrState,=20 >>> p.AddrZip, >>> p.Gender,=20 >>> p.DateOfBirth, >>> -- Provider Info >>> af.IdAffil, >>> af.PractName,=20 >>> af.OfficeName, >>> -- Exam Info >>> e.IdExam, >>> e.dateexam,=20 >>> a.WorkArea dept,=20 >>> a.Room location,=20 >>> e.ProcModeCode, >>> e.ProcName, >>> e.IdRefSite, >>> ec.IdCPT, >>> e.zzk exam_zzk, >>> 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 , '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(), 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), and add this: >>>=20 >>> AND e.dateexam =3D '2012-08-13' >>>=20 >>> it runs like lightning, super fast. But if I run the query against = the view, 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 going on, 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 >> =20 >=20 >=20 > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql >=20