CiviCRM Community Forums (archive)

I'm running into this same problem with the Advanced Search - I'm using CiviCRM 3.2.1 on Ubuntu 10.04 using MySQL 5.1 something.

Anyway, I found out that it's taking forever (over 30 minutes) for a database containing over 500k records - and it doesn't seem to be using the indices because there are indices out there for some of them.

I'm searching on a name - I know the first name and last name to search on - and here's the SELECT statement that's generated out of the Advanced Search

SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.first_name  as `first_name`, contact_a.last_name  as `last_name`  FROM civicrm_contact contact_a WHERE  (  ( LOWER(contact_a.first_name) = 'terry' AND LOWER(contact_a.last_name) = 'scott' AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0)   ORDER BY contact_a.sort_name asc  LIMIT 0, 50
I tried to run that from a mysql command line - takes forever.  I then remove the LOWER() for the first_name and last_name fields, and it takes  change it to

SELECT DISTINCT(contact_a.id) as contact_id, contact_a.contact_type  as `contact_type`, contact_a.contact_sub_type  as `contact_sub_type`, contact_a.sort_name  as `sort_name`, contact_a.first_name  as `first_name`, contact_a.last_name  as `last_name`  FROM civicrm_contact contact_a WHERE  (  ( contact_a.first_name = 'terry' AND contact_a.last_name = 'scott' AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0)   ORDER BY contact_a.sort_name asc  LIMIT 0, 50
and it runs in 0.25 seconds.  I've tried running it with the values (first name and last name) all caps, initial caps and some caps and it all returns the same records in about 0.25 seconds which means it's using the indices.

The minute I put in "LOWER(first_name)" it bypasses the index and reads each record.

I'm going to try and make some changes in the underlying PHP to see if I can get it to run a little faster.