Slow queries due to use of LOWER() in mysql searches, fix contribution search, current employer. Comment elsewhere

Fix contribution search to not use performance degrading LOWER function, which gives no benefit. Also fix current_employer field. Comment other instances.

Note I have chosen just to remove some this round & remove more later in case (against all analysis) someone experiences a problem. However, I intend to keep removing over time.

I am noticing some slow queries in CiviCRM because of the use of LOWER(), notably around the credit_note search. Analysis in 2013 concluded this was not required & resulted in a patch that was not merged under CRM-12436.

Using LOWER() automatically bypasses any index on the field, so it should only be used if it is required.

If you are using a case insensitive collation then the search is case insensitive already and the addition of LOWER provides slowness without functionality change ie

elseif ($name === 'current_employer') {

$value = $strtolower(CRM_Core_DAO::escapeString($value));

if ($wildcard) { bq. $op = 'LIKE'; bq. $value = self::getWildCardedValue($wildcard, $op, $value); bq. }

$wc = self::caseImportant($op) ? "LOWER(contact_a.organization_name)" : "contact_a.organization_name";

is no different to

elseif ($name === 'current_employer') {

if ($wildcard) { bq. $op = 'LIKE'; bq. $value = self::getWildCardedValue($wildcard, $op, $value); bq. }

$wc = "contact_a.organization_name";

I checked to see if we were using entirely case insensitive collation on text fields. It turns out the suffix '_ci' means case insensitive - so uft_general_ci & utf_unicode_ci are both case insensitive (http://weblogs.sqlteam.com/dang/archive/2009/07/26/Collation-Hell-Part-1.aspx) so we are not case sensitive.

I did a check on my DB to see if there were any other types & did not find them - ie

SELECT DISTINCT collation_name FROM information_schema.columns
WHERE data_type NOT IN ('bigint', 'int', 'tinyint', 'date', 'datetime', 'decimal', 'double', 'timestamp', 'blob', 'mediumblob')
AND table_name LIKE 'civicrm%'
AND table_name NOT LIKE 'civicrm_word_replacement'
#AND collation_name NOT LIKE '%_ci'
ORDER BY collation_name

So, my intention is to remove the slow unindexed LOWER functions