Hello,
I have been investigating the lack of speed a lot and I have found that there are missing indexes in the “openemr_postcalendar_events” table.
The query is in /interface/main/calendar/PostCalendar/pnuserapi.php, fuction & postcalendar_userapi_pcQueryEvents (& args)
In my tests this query was made:
SELECT DISTINCT a.pc_eid, a.pc_informant, a.pc_catid, a.pc_title, a.pc_time, a.pc_hometext, a.pc_eventDate,
a.pc_duration, a.pc_endDate, a.pc_startTime, a.pc_recurrtype, a.pc_recurrfreq, a.pc_recurrspec, a.pc_topic,
a.pc_alldayevent, a.pc_location, a.pc_conttel, a.pc_contname, a.pc_contemail, a.pc_website, a.pc_fee, a.pc_sharing,
a.pc_prefcatid, b.pc_catcolor, b.pc_catname, b.pc_catdesc, a.pc_pid, a.pc_apptstatus, a.pc_aid,
concat (u.fname, ‘’, u.lname) as provider_name, concat (pd.lname, ‘,’, pd.fname) as patient_name,
concat (u2.fname, ‘’, u2.lname) as owner_name, DOB as patient_dob, a.pc_facility, pd.pubpid,
ins.title as insurance_name, tg.group_name, tg.group_type, tg.group_status
FROM (openemr_postcalendar_events AS a) LEFT JOIN openemr_postcalendar_categories AS b ON b.pc_catid = a.pc_catid
LEFT JOIN users as or ON a.pc_aid = u.id
LEFT JOIN users as u2 ON a.pc_aid = u2.id
LEFT JOIN patient_data as pd ON a.pc_pid = pd.pid
LEFT JOIN list_options as ins ON pd.iid = ins.option_id and ins.list_id = ‘Secure_Suppliers’
LEFT JOIN therapy_groups as tg ON a.pc_gid = tg.group_id
WHERE a.pc_eventstatus = 1
AND ((a.pc_endDate> = ‘2019-09-17’ AND a.pc_eventDate <= ‘2019-09-17’)
OR (a.pc_endDate = ‘0000-00-00’ AND a.pc_eventDate> = ‘2019-09-17’ AND a.pc_eventDate <= ‘2019-09-17’))
AND a.pc_aid in (0.6)
GROUP BY a.pc_eid
ORDER BY a.pc_startTime ASC, a.pc_catid ASC, a.pc_duration ASC
You can change dates, pd_aid (user id) …
Without index it takes 0.610 seconds, it goes through all the records of the openemr_postcalendar_events table, about 60,000 records.
Adding these indexes:
ALTER TABLE openemr
.openemr_postcalendar_events
ADD INDEXidx_openemr_postcalendar_events_eventList
(pc_eventstatus, pc_endDate, pc_eventDate, pc_aid) USING BTREE;
ALTER TABLE openemr
.openemr_postcalendar_events
ADD INDEXidx_openemr_postcalendar_events_pc_pid
(pc_pid
) USING BTREE;
It takes 0.016 seconds to run only 110 records.
Could you try to see if it improves calendar performance?
Could I have lost these indexes?
Thank you
PS: Sorry if it’s not well written, my native language is Spanish