tmccormi wrote on Thursday, September 20, 2012:
This query which I think is from the appoint encounters report is getting logged as an issue on my server and my be causing mysqld timeout/connection issues. Googling similar intermittant connection problems with mysql indicate that this is possible cause and fixing the query can resolve it… Thoughts?
# Time: 120920 12:44:17
# User@Host: emr_betterlife[emr_betterlife] @ localhost []
# Query_time: 2.340384 Lock_time: 0.001485 Rows_sent: 35 Rows_examined: 5010081
SET timestamp=1348170257;
( SELECT e.pc_eventDate, e.pc_startTime, fe.encounter, fe.date AS encdate, f.authorized, p.fname, p.lname, p.pid, p.pubpid, CONCAT( u.lname, ', ', u.fname ) AS docname FROM openemr_postcalendar_events AS e LEFT OUTER JOIN form_encounter AS fe ON LEFT(fe.date, 10) = e.pc_eventDate AND fe.pid = e.pc_pid LEFT OUTER JOIN forms AS f ON f.encounter = fe.encounter AND f.formdir = 'newpatient' LEFT OUTER JOIN patient_data AS p ON p.pid = e.pc_pid LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE e.pc_eventDate >= '2012-09-20' AND e.pc_eventDate <= '2012-09-20' AND e.pc_pid != '' AND e.pc_apptstatus != '?' ) UNION ( SELECT e.pc_eventDate, e.pc_startTime, fe.encounter, fe.date AS encdate, f.authorized, p.fname, p.lname, p.pid, p.pubpid, CONCAT( u.lname, ', ', u.fname ) AS docname FROM form_encounter AS fe LEFT OUTER JOIN openemr_postcalendar_events AS e ON LEFT(fe.date, 10) = e.pc_eventDate AND fe.pid = e.pc_pid AND e.pc_pid != '' AND e.pc_apptstatus != '?' LEFT OUTER JOIN forms AS f ON f.encounter = fe.encounter AND f.formdir = 'newpatient' LEFT OUTER JOIN patient_data AS p ON p.pid = fe.pid LEFT OUTER JOIN users AS u ON u.id = fe.provider_id WHERE fe.date >= '2012-09-20 00:00:00' AND fe.date <= '2012-09-20 23:59:59' ) ORDER BY docname, pc_eventDate, pc_startTime;