Mysql Slow Logs

OpenEMR Development
1

DB/Query optimization:

I had the opportunity to review the mysql-slow.log from a large practice using OpenEMR and there were some queries that are causing bottlenecks. Can anyone look at these and help decide what needs to be added, such as indexes to these tables to speed them up?

# Time: 2019-05-20T15:02:37.392370Z
# User@Host: openemr[openemr] @ localhost Id: 148154
# Query_time: 8.207200 Lock_time: 0.000313 Rows_sent: 0 Rows_examined: 1310764
SET timestamp=1558364557;
SELECT value FROM clinical_rules_log WHERE category = ‘clinical_reminder_widget’ AND pid = 116354 AND uid = ‘2053’ ORDER BY id DESC LIMIT 1;

# Time: 2019-05-20T15:03:21.818403Z
# User@Host: openemr[openemr] @ localhost Id: 148103
# Query_time: 8.551393 Lock_time: 0.000181 Rows_sent: 1 Rows_examined: 135721
SET timestamp=1558364601;
SELECT id, form_id, content FROM form_repository WHERE pid=158426 AND form_id=‘71101’ AND form_name=‘form_ped_comp1’ AND encounter=935930;

# Time: 2019-05-20T16:23:46.532411Z
# User@Host: openemr[openemr] @ localhost Id: 199866
# Query_time: 7.751776 Lock_time: 0.000407 Rows_sent: 0 Rows_examined: 1312033
SET timestamp=1558369426;
SELECT value FROM clinical_rules_log WHERE category = ‘clinical_reminder_widget’ AND pid = 1000000000001775 AND uid = ‘2053’ ORDER BY id DESC LIMIT 1;

2

We must have added uid, pid and category as a single multi-column index. I think standard code base has 4 indexes for 4 columns. Not sure how much that will help since that’s not that huge a table for mysql to handle.

More importantly you should look at value of keeping 1.3M rows. They probably don’t need ability to review alerts shown to an user years ago. Better yet, do confirm they really need all alerts.