@dkayiwa Thanks for getting back and sorry for a late reply. Apparently the issues you have mentioned is exactly what we were facing. However there is another observation for us. I ran the bare queries on MySQL with and without lower() and below are the results.
The query generated by Platform 1.11.5 without any modification took 3.17 minutes:
select
this_.patient_id as person1_216_4_,
this_1_.uuid as uuid216_4_,
this_1_.gender as gender216_4_,
this_1_.birthdate as birthdate216_4_,
this_1_.birthdate_estimated as birthdate5_216_4_,
this_1_.birthtime as birthtime216_4_,
this_1_.dead as dead216_4_,
this_1_.death_date as death8_216_4_,
this_1_.deathdate_estimated as deathdate9_216_4_,
this_1_.cause_of_death as cause10_216_4_,
this_1_.creator as creator216_4_,
this_1_.date_created as date12_216_4_,
this_1_.changed_by as changed13_216_4_,
this_1_.date_changed as date14_216_4_,
this_1_.voided as voided216_4_,
this_1_.voided_by as voided16_216_4_,
this_1_.date_voided as date17_216_4_,
this_1_.void_reason as void18_216_4_,
this_.patient_id as patient1_229_4_,
this_.creator as creator229_4_,
this_.date_created as date3_229_4_,
this_.changed_by as changed4_229_4_,
this_.date_changed as date5_229_4_,
this_.voided as voided229_4_,
this_.voided_by as voided7_229_4_,
this_.date_voided as date8_229_4_,
this_.void_reason as void9_229_4_,
case when exists (select * from patient p where p.patient_id = this_1_.person_id) then 1
else 0
end as formula1_4_,
name1_.person_name_id as person1_221_0_,
name1_.uuid as uuid221_0_,
name1_.preferred as preferred221_0_,
name1_.person_id as person4_221_0_,
name1_.prefix as prefix221_0_,
name1_.given_name as given6_221_0_,
name1_.middle_name as middle7_221_0_,
name1_.family_name_prefix as family8_221_0_,
name1_.family_name as family9_221_0_,
name1_.family_name2 as family10_221_0_,
name1_.family_name_suffix as family11_221_0_,
name1_.degree as degree221_0_, name1_.creator as creator221_0_,
name1_.date_created as date14_221_0_,
name1_.changed_by as changed15_221_0_,
name1_.date_changed as date16_221_0_,
name1_.voided_by as voided17_221_0_,
name1_.date_voided as date18_221_0_,
name1_.void_reason as void19_221_0_,
name1_.voided as voided221_0_,
attribute2_.person_id as person3_216_6_,
attribute2_.person_attribute_id as person1_6_,
attribute2_.person_attribute_id as person1_217_1_,
attribute2_.uuid as uuid217_1_,
attribute2_.person_id as person3_217_1_,
attribute2_.person_attribute_type_id as person4_217_1_,
attribute2_.value as value217_1_,
attribute2_.creator as creator217_1_,
attribute2_.date_created as date7_217_1_,
attribute2_.changed_by as changed8_217_1_,
attribute2_.date_changed as date9_217_1_,
attribute2_.voided_by as voided10_217_1_,
attribute2_.date_voided as date11_217_1_,
attribute2_.voided as voided217_1_,
attribute2_.void_reason as void13_217_1_,
attributet3_.person_attribute_type_id as person1_218_2_,
attributet3_.uuid as uuid218_2_,
attributet3_.name as name218_2_,
attributet3_.description as descript4_218_2_,
attributet3_.format as format218_2_,
attributet3_.foreign_key as foreign6_218_2_,
attributet3_.sort_weight as sort7_218_2_,
attributet3_.searchable as searchable218_2_,
attributet3_.creator as creator218_2_,
attributet3_.date_created as date10_218_2_,
attributet3_.changed_by as changed11_218_2_,
attributet3_.date_changed as date12_218_2_,
attributet3_.retired_by as retired13_218_2_,
attributet3_.date_retired as date14_218_2_,
attributet3_.retire_reason as retire15_218_2_,
attributet3_.retired as retired218_2_,
attributet3_.edit_privilege as edit17_218_2_,
ids4_.patient_id as patient3_216_7_,
ids4_.patient_identifier_id as patient1_7_,
ids4_.patient_identifier_id as patient1_230_3_,
ids4_.uuid as uuid230_3_, ids4_.patient_id as patient3_230_3_,
ids4_.identifier as identifier230_3_,
ids4_.identifier_type as identifier5_230_3_,
ids4_.location_id as location6_230_3_,
ids4_.date_created as date7_230_3_,
ids4_.date_voided as date8_230_3_,
ids4_.preferred as preferred230_3_,
ids4_.voided as voided230_3_,
ids4_.void_reason as void11_230_3_,
ids4_.creator as creator230_3_,
ids4_.voided_by as voided13_230_3_,
ids4_.changed_by as changed14_230_3_,
ids4_.date_changed as date15_230_3_
from
patient this_ inner join person this_1_ on this_.patient_id=this_1_.person_id
inner join person_name name1_ on this_.patient_id=name1_.person_id
left outer join person_attribute attribute2_ on this_.patient_id=attribute2_.person_id
left outer join person_attribute_type attributet3_ on attribute2_.person_attribute_type_id=attributet3_.person_attribute_type_id
left outer join patient_identifier ids4_ on this_.patient_id=ids4_.patient_id
where
(((name1_.voided=0 and (name1_.given_name like 'wafula' or name1_.middle_name like 'wafula' or name1_.family_name like 'wafula' or name1_.family_name2 like 'wafula')))
or ((attributet3_.searchable=1 and attribute2_.voided=0 and lower(attribute2_.value) like 'wafula'))
or (ids4_.voided=0 and lower(ids4_.identifier) like 'wafula'))
and this_.voided=0
order by attribute2_.voided asc, attribute2_.date_created desc, ids4_.voided asc, ids4_.preferred desc, ids4_.identifier_type desc,
ids4_.date_created desc, name1_.given_name asc, name1_.middle_name asc, name1_.family_name asc;
Modifying the above query removing the lower() mysql function made the query to take about 46 seconds which is a massive improvement. However this is too long for practical purposes so I decided to rid attribute searches altogether because we don’t actually search for attributes at AMPATH (or so it seems). The performance improved further down to about 10 seconds. But then again this is still not enough so I decided to separate the name searches from identifier searches, finally this gave results near instantaneously:
https://github.com/mhawila/openmrs-core/commit/47f108046cde27b98d3055edaaac9ea9f7ca8d6b
Remarks & Afterthoughts
When going through the HibernatePatientDAO class I realize there is a lot of flexibilities provided by the API which is not accessible via UI. I was thinking it could be better to expose these functionalities to provide the users with enough options to tweak the patient search to suit their purposes. I had to manually do the quick fix because the default implementation assumes several things which may be okay for majority of purposes but not exactly suitable for some few implementation like AMPATH.
Any thoughts on this would be nice.
Thanks.