CiviCRM Community Forums (archive)

> Is it possible that civicrm doesn't use Drupal's standard database query functions (and thus is excluded from devel's query monitory)?

Not only is this possible, but this is precisely the case--your guess is correct.

> How is it possible that devel tells me that the page was generated in less than 2 seconds, but that it takes over 30 to get to my computer, this with a VPS and a broadband connection?

I don't know devel well enough to answer that, but regarding the latency, you will need to use more low-level diagnostics to determine what's slow. In particular you scould enable MySQL query logging and examine the logs.

Hi,

Thanks to everyone for your prompt and useful replies.

To enable slow query logging, I following the instructions in http://forums.hostsearch.com/showthread.php?3048-To-enable-slow-query-logging-of-mysql

The result is:

/usr/sbin/mysqld, Version: 5.0.91-community-log (MySQL Community Edition (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
# Time: 100821  9:33:43
# User@Host: qfhsa_website[qfhsa_website] @ localhost []
# Query_time: 28  Lock_time: 0  Rows_sent: 18  Rows_examined: 16634752
use qfhsa_civicrm;
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.display_name  as `display_name`, contact_a.do_not_email  as `do_not_email`, contact_a.do_not_phone  as `do_not_phone`, contact_a.do_not_mail  as `do_not_mail`, contact_a.do_not_sms  as `do_not_sms`, contact_a.do_not_trade  as `do_not_trade`, contact_a.is_opt_out  as `is_opt_out`, contact_a.legal_identifier  as `legal_identifier`, contact_a.external_identifier  as `external_identifier`, contact_a.nick_name  as `nick_name`, contact_a.legal_name  as `legal_name`, contact_a.home_URL  as `home_URL`, contact_a.image_URL  as `image_URL`, contact_a.preferred_mail_format  as `preferred_mail_format`, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.job_title  as `job_title`, contact_a.birth_date  as `birth_date`, contact_a.is_deceased  as `is_deceased`, contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) AS organization_name, contact_a.sic_code  as `sic_code`, gender.value as gender_id, gender.label as gender, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) AS current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_im.id as im_id, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`  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  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id  LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id) LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  WHERE  ( ( contact_b.sort_name LIKE '%arundel%' AND contact_b.id != contact_a.id ) AND civicrm_relationship.is_active = 1 AND ( civicrm_relationship.end_date is NULL OR civicrm_relationship.end_date >= 20100821 ) AND civicrm_relationship.relationship_type_id = 11 )  AND  ( 1 )    ORDER BY contact_a.sort_name asc  LIMIT 0, 50;

Performing this query from the command line I can confirm that that query taking way too much time.

Cheers,

Albert.

how big is your DB

1. number of contacts
2. number of addresses
3. number of relationships

there is already in  index on civicrm_contact(id) (all FK's are indexed)

Also where are u hosting your site, i..e how loaded is your db server

thanx

lobo

what version are u on?

can u upgrade to 3.2.1 and check if the problem is still the same

if so, can u please email me your db: lobo at yahoo dot com

lobo

The query in 3.2 is slightly different from the one posted above (for 3.0). Here is the correct query for 3.2:


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.display_name  as `display_name`, contact_a.do_not_email  as `do_not_email`, contact_a.do_not_phone  as `do_not_phone`, contact_a.do_not_mail  as `do_not_mail`, contact_a.do_not_sms  as `do_not_sms`, contact_a.do_not_trade  as `do_not_trade`, contact_a.is_opt_out  as `is_opt_out`, contact_a.legal_identifier  as `legal_identifier`, contact_a.external_identifier  as `external_identifier`, contact_a.nick_name  as `nick_name`, contact_a.legal_name  as `legal_name`, contact_a.image_URL  as `image_URL`, contact_a.preferred_mail_format  as `preferred_mail_format`, contact_a.first_name  as `first_name`, contact_a.middle_name  as `middle_name`, contact_a.last_name  as `last_name`, contact_a.job_title  as `job_title`, contact_a.birth_date  as `birth_date`, contact_a.is_deceased  as `is_deceased`, contact_a.deceased_date  as `deceased_date`, contact_a.household_name  as `household_name`, IF ( contact_a.contact_type = 'Individual', NULL, contact_a.organization_name ) as organization_name, contact_a.sic_code  as `sic_code`, gender.value as gender_id, gender.label as gender, individual_prefix.value as individual_prefix_id, individual_prefix.label as individual_prefix, individual_suffix.value as individual_suffix_id, individual_suffix.label as individual_suffix, IF ( contact_a.contact_type = 'Individual', contact_a.organization_name, NULL ) as current_employer, civicrm_address.id as address_id, civicrm_address.street_address as `street_address`, civicrm_address.supplemental_address_1 as `supplemental_address_1`, civicrm_address.supplemental_address_2 as `supplemental_address_2`, civicrm_address.city as `city`, civicrm_address.postal_code_suffix as `postal_code_suffix`, civicrm_address.postal_code as `postal_code`, civicrm_address.geo_code_1 as `geo_code_1`, civicrm_address.geo_code_2 as `geo_code_2`, civicrm_state_province.id as state_province_id, civicrm_state_province.abbreviation as `state_province`, civicrm_state_province.name as state_province_name, civicrm_country.id as country_id, civicrm_country.name as `country`, civicrm_phone.id as phone_id, civicrm_phone.phone_type_id as phone_type_id, civicrm_phone.phone as `phone`, civicrm_email.id as email_id, civicrm_email.email as `email`, civicrm_email.on_hold as `on_hold`, civicrm_im.id as im_id, civicrm_im.provider_id as provider_id, civicrm_im.name as `im`, civicrm_worldregion.id as worldregion_id, civicrm_worldregion.name as `world_region`  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  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id  LEFT JOIN civicrm_relationship ON (civicrm_relationship.contact_id_b = contact_a.id OR civicrm_relationship.contact_id_a = contact_a.id) LEFT JOIN civicrm_contact contact_b ON (civicrm_relationship.contact_id_a = contact_b.id OR civicrm_relationship.contact_id_b = contact_b.id) LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  LEFT JOIN civicrm_option_group option_group_prefix ON (option_group_prefix.name = 'individual_prefix') LEFT JOIN civicrm_option_value individual_prefix ON (contact_a.prefix_id = individual_prefix.value AND option_group_prefix.id = individual_prefix.option_group_id )  LEFT JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix') LEFT JOIN civicrm_option_value individual_suffix ON (contact_a.suffix_id = individual_suffix.value AND option_group_suffix.id = individual_suffix.option_group_id )  WHERE  ( ( contact_b.sort_name LIKE '%arundel%' AND contact_b.id != contact_a.id ) AND civicrm_relationship.is_active = 1 AND ( civicrm_relationship.end_date is NULL OR civicrm_relationship.end_date >= 20100821 ) AND civicrm_relationship.relationship_type_id = 11 )  AND (contact_a.is_deleted = 0)   ORDER BY contact_a.sort_name asc  LIMIT 0, 50;

Thanks lobo for taking the time to look into this.

Does anyone think adding an idex to the 3 left joins mentioned by lobo might make this query faster without creating a custom search.

Cheers,

Albert.

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
"explain" the query goes like that

| id | select_type | table                  | type   | possible_keys                                                                                                          | key                                          | key_len | ref                                         | rows | Extra                                          |
+----+-------------+------------------------+--------+------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------+------+------------------------------------------------+
|  1 | SIMPLE      | contact_a              | ref    | PRIMARY,index_is_deleted                                                                                               | index_is_deleted                             | 1       | const                                       |    7 | Using temporary; Using filesort                |
|  1 | SIMPLE      | civicrm_relationship   | ref    | FK_civicrm_relationship_contact_id_a,FK_civicrm_relationship_contact_id_b,FK_civicrm_relationship_relationship_type_id | FK_civicrm_relationship_relationship_type_id | 4       | const                                       |    1 | Using where                                    |
|  1 | SIMPLE      | civicrm_address        | ref    | index_is_primary,FK_civicrm_address_contact_id                                                                         | FK_civicrm_address_contact_id                | 5       | etownhall.contact_a.id                      |    1 |                                                |
|  1 | SIMPLE      | civicrm_state_province | eq_ref | PRIMARY                                                                                                                | PRIMARY                                      | 4       | etownhall.civicrm_address.state_province_id |    1 |                                                |
|  1 | SIMPLE      | civicrm_country        | eq_ref | PRIMARY                                                                                                                | PRIMARY                                      | 4       | etownhall.civicrm_address.country_id        |    1 |                                                |
|  1 | SIMPLE      | civicrm_email          | ref    | index_is_primary,FK_civicrm_email_contact_id                                                                           | FK_civicrm_email_contact_id                  | 5       | etownhall.contact_a.id                      |    1 |                                                |
|  1 | SIMPLE      | civicrm_phone          | ref    | index_is_primary,FK_civicrm_phone_contact_id                                                                           | FK_civicrm_phone_contact_id                  | 5       | etownhall.contact_a.id                      |    1 |                                                |
|  1 | SIMPLE      | civicrm_im             | ref    | index_is_primary,FK_civicrm_im_contact_id                                                                              | FK_civicrm_im_contact_id                     | 5       | etownhall.contact_a.id                      |    1 |                                                |
|  1 | SIMPLE      | contact_b              | ALL    | PRIMARY                                                                                                                | NULL                                         | NULL    | NULL                                        |   14 | Range checked for each record (index map: 0x1) |
|  1 | SIMPLE      | civicrm_worldregion    | eq_ref | PRIMARY                                                                                                                | PRIMARY                                      | 4       | etownhall.civicrm_country.region_id         |    1 |                                                |
|  1 | SIMPLE      | option_group_gender    | const  | UI_name                                                                                                                | UI_name                                      | 194     | const                                       |    1 | Using index                                    |
|  1 | SIMPLE      | gender                 | ref    | index_option_group_id_value,index_option_group_id_name                                                                 | index_option_group_id_name                   | 4       | etownhall.option_group_gender.id            |    2 |                                                |
|  1 | SIMPLE      | option_group_prefix    | const  | UI_name                                                                                                                | UI_name                                      | 194     | const                                       |    1 | Using index                                    |
|  1 | SIMPLE      | individual_prefix      | ref    | index_option_group_id_value,index_option_group_id_name                                                                 | index_option_group_id_name                   | 4       | etownhall.option_group_prefix.id            |    2 |                                                |
|  1 | SIMPLE      | option_group_suffix    | const  | UI_name                                                                                                                | UI_name                                      | 194     | const                                       |    1 | Using index                                    |
|  1 | SIMPLE      | individual_suffix      | ref    | index_option_group_id_value,index_option_group_id_name                                                                 | index_option_group_id_name                   | 4       | etownhall.option_group_suffix.id            |    2 |                                                |
+----+-------------+------------------------+--------+------------------------------------------------------------------------------------------------------------------------+----------------------------------------------+---------+---------------------------------------------+------+------------------------------------------------+
16 rows in set (0.11 sec)


Couple of points from long ago when this blob made almost sense to me:

1) These messages:

• Using temporary; Using filesort
• Range checked for each record (index map: 0x1)
  

Are usually not good and means it doesn't use the indexes all the time.

2) It fetches a lot of external references (eg state, worldregion,gender...). Are they all fields you requested to see (and want) or the default query fetches everything, even if it's not going to be exported.

3) When you search for "%arundel%" that is rather expensive by itself, does it mean that you want to find all the contacts of that name (or aaarundel or arundellll or... , then find their relationship, or that you already found a contact ?


Something that could speed up the searches are that instead of searching all the related contacts by their name when there is only one is to skip the contact_b search, and directly use the contact id on the relationship.

X+