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
On Aug 23, 2012, at 2:06 PM, James W. McNeely wrote:
> This didn't help, but good try!
>
> Jim McNeely
>
> On Aug 23, 2012, at 12:27 PM, Martin Gainty wrote:
>
>>
>> If memory serves predicates convert strings to column-data-type (in your
>> case DATE) this *should* help
>> WHERE dateexam = STR_TO_DATE('2012-08-13','%Y-%m-%d')
>> does this help?
>> Martin
>> ______________________________________________
>> Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
>>
>> 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 être privilégié. Si vous n'êtes pas le
>> destinataire prévu, nous te demandons avec bonté que pour satisfaire
>> informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
>> de ceci est interdite. Ce message sert à l'information seulement et n'aura
>> pas n'importe quel effet légalement obligatoire. Étant donné que les email
>> peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
>> aucune responsabilité pour le contenu fourni.
>>
>>
>>> From: j...@newcenturydata.com
>>> Subject: view query is slow
>>> Date: Thu, 23 Aug 2012 11:30:17 -0700
>>> To: mysql@lists.mysql.com
>>>
>>> I am working on a view based on this query:
>>>
>>> =======================================
>>> SELECT
>>> -- Patient Info
>>> p.IdPatient,
>>> p.IdLastword MRN,
>>> p.NameLast,
>>> p.NameFirst,
>>> p.Addr1,
>>> p.Addr2,
>>> p.AddrCity,
>>> p.AddrState,
>>> p.AddrZip,
>>> p.Gender,
>>> p.DateOfBirth,
>>> -- Provider Info
>>> af.IdAffil,
>>> af.PractName,
>>> af.OfficeName,
>>> -- Exam Info
>>> e.IdExam,
>>> e.dateexam,
>>> a.WorkArea dept,
>>> a.Room location,
>>> 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 = p.IdPatient
>>> LEFT JOIN Examcpt_ ec ON (e.IdExam = ec.IdExam AND '1' = Quantity)
>>> LEFT JOIN Copy_ c ON CONCAT(e.IdAppt , '0') = c.IdApptType
>>> LEFT JOIN Appt_ a ON e.IdAppt = a.IdAppt
>>> LEFT JOIN Affil_ af ON c.IdPractAffil = af.IdAffil
>>> WHERE
>>> p.AddrState = 'WA'
>>> AND e.statusnumber = '4'
>>> AND e.IdRefSite <> 'S50'
>>> AND e.IdRefSite <> 'S51'
>>> AND e.IdREfSite <> 'S63'
>>> AND p.DateOfBirth < DATE_ADD(CURDATE(), INTERVAL '-2' MONTH)
>>> AND a.zzk IS NOT NULL
>>> ============================================
>>>
>>> If I run this query itself (not in the view), and add this:
>>>
>>> AND e.dateexam = '2012-08-13'
>>>
>>> it runs like lightning, super fast. But if I run the query against the
>>> view, for example "SELECT * FROM exam_view WHERE dateexam = '2012-08-13'
>>>
>>> It is so glacially slow that I end up having to kill the query. What is
>>> going on, and how can I fix this?
>>>
>>> Jim McNeely
>>> Northwest Radiologists
>>> Senior Database Programmer
>>> 360-788-9022 desk
>>> 360-303-3332 mobile
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql
>>>
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
