CiviCRM Community Forums (archive)

I'm starting to use some databases with 500K + records and while checking out some of the data loads I performed, noticed that some of the queries that are built force MySQL to read through each record.

One great example is in the Search Builder section.  If you select certain fields, it forces the search criteria to all lower case, then forces the database field to LOWER which then causes the query to read each record.  For example:

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE  (  ( LOWER(civicrm_state_province.name) = 'maryland' AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0) ;
Takes about 18 seconds to run against 500K records while

SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE  (  ( civicrm_state_province.name = 'maryland' AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0) ;
Takes about 2 seconds and returns the same values.  I've also tried it with 'MARYLAND' and 'Maryland' and 'MaryLand' - all with the same results.

I'm also looking at the Advanced Search - I have a feeling that there might be a few more...

I'm very new to trying to add back to open source projects, so please feel free to let me know the correct way of doing things!

how about this query

SELECT SQL_NO_CACHE DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  WHERE  (  ( civicrm_state_province.name LIKE 'maryland%' AND contact_a.contact_type IN ('Individual') )  )  AND (contact_a.is_deleted = 0) ;


lobo

In general for something like search builder giving the user a lot more options makes it a lot more flexible. it also makes the code a lot harder to write/debug/understand

not sure what direction we should be going for search builder. It can get incredibly complex and maybe the views2 integration does most of what it does?

lobo