Significant performance improvement identified

[Eileen]

I have seen ongoing speed issues with some CiviCRM searches in CiviCRM - in particular the quick search & simple search

I did some investigating & found that this query (a cut down & simplified version of the real one) was examining 3347350 records - 81 times the number of records in the contact table. I was able to reduce the number to 74826 (still more but better) by playing with column types

Code: [Select]

EXPLAIN EXTENDED SELECT  *
FROM civicrm_contact contact_a
LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)
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 (option_group_prefix.id = individual_prefix.option_group_id AND contact_a.prefix_id = individual_prefix.value )
LEFT JOIN civicrm_option_group option_group_suffix ON (option_group_suffix.name = 'individual_suffix')
LEFT JOIN civicrm_option_value individual_suffix ON ( option_group_suffix.id = individual_suffix.option_group_id AND contact_a.suffix_id = individual_suffix.value )
WHERE ((((contact_a.sort_name LIKE '%civi%') OR (civicrm_email.email LIKE '%civi%'))
))
AND (contact_a.is_deleted = 0)

ORDER BY contact_a.sort_name ASC
LIMIT 0, 50;
EXPLAIN BEFORE

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	contact_a	index	index_is_deleted	index_sort_name	387	NULL	2	3347350.00	Using where
1	SIMPLE	civicrm_email	ref	index_is_primary,FK_civicrm_email_contact_id	FK_civicrm_email_contact_id	5	au_drupal.contact_a.id	1	100.00	Using where
1	SIMPLE	option_group_gender	const	UI_name	UI_name	194	const	1	100.00
1	SIMPLE	gender	ref	FK_civicrm_option_value_option_group_id,index_option_group_id_name,index_option_group_id_value	FK_civicrm_option_value_option_group_id	4	au_drupal.option_group_gender.id	6	100.00
1	SIMPLE	option_group_prefix	const	UI_name	UI_name	194	const	1	100.00
1	SIMPLE	individual_prefix	ref	FK_civicrm_option_value_option_group_id,index_option_group_id_name,index_option_group_id_value	FK_civicrm_option_value_option_group_id	4	au_drupal.option_group_prefix.id	6	100.00
1	SIMPLE	option_group_suffix	const	UI_name	UI_name	194	const	1	100.00
1	SIMPLE	individual_suffix	ref	FK_civicrm_option_value_option_group_id,index_option_group_id_name,index_option_group_id_value	FK_civicrm_option_value_option_group_id	4	au_drupal.option_group_suffix.id	6	100.00

AND AFTER

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	contact_a	index	index_is_deleted	index_sort_name	387	NULL	100	74826.00	Using where
1	SIMPLE	civicrm_email	ref	index_is_primary,FK_civicrm_email_contact_id	FK_civicrm_email_contact_id	5	au_drupal.contact_a.id	1	100.00	Using where
1	SIMPLE	option_group_gender	const	UI_name	UI_name	194	const	1	100.00
1	SIMPLE	gender	ref	index_option_group_id_value,FK_civicrm_option_value_option_group_id,index_option_group_id_name	index_option_group_id_value	772	au_drupal.contact_a.gender_id,au_drupal.option_group_gender.id	1	100.00
1	SIMPLE	option_group_prefix	const	UI_name	UI_name	194	const	1	100.00
1	SIMPLE	individual_prefix	ref	index_option_group_id_value,FK_civicrm_option_value_option_group_id,index_option_group_id_name	index_option_group_id_value	772	au_drupal.contact_a.prefix_id,au_drupal.option_group_prefix.id	1	100.00
1	SIMPLE	option_group_suffix	const	UI_name	UI_name	194	const	1	100.00
1	SIMPLE	individual_suffix	ref	index_option_group_id_value,FK_civicrm_option_value_option_group_id,index_option_group_id_name	index_option_group_id_value	772	au_drupal.contact_a.suffix_id,au_drupal.option_group_suffix.id	1	100.00

Note that it in the first example it is not using the correct index to join against the option_value table and the #rows in each line says 6 (even though there are only 3 gender_ids - I found this number was as high as 14 on one install!)

THE PROBLEM

civicrm_option_value.value is a VARCHAR
civicrm_contact.gender_id & the other two are INTEGERS. Seems intuitively like they should be - but ... they shouldn't ... not if they are to join on the option_value.value field

[Eileen]

So, running the full query on 2 different copies of the same DB on the same server

Code: [Select]

SELECT SQL_NO_CACHE 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`, contact_a.is_deleted AS `contact_is_deleted`, 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_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_a.sort_name LIKE '%grogan%') OR (civicrm_email.email LIKE '%grogan%'))
OR ((contact_a.sort_name LIKE '%a%') OR (civicrm_email.email LIKE '%a%'))))
AND (contact_a.is_deleted = 0)
GROUP BY contact_a.id
ORDER BY contact_a.sort_name ASC
LIMIT 0, 50;
On the DB with no change I get
7.161 sec
8.221 sec
9,048 sec
8,253 sec
9,188 sec

the DB with the change
5.695
4.773
5.506
4.555
4.243
4.150

[Eileen]

Reverting the change caused the changed DB to go back to speeds between 8.767 & 9.314

[xavier]

Are gender, im and word region displayed or used in these specific searches?

This being said, they are in general and indeed, being able to join them while using an index makes sense. While we are at it if we go from int to varchar.

1) can it be varchar(1) and make the join?
2) couldn't we use a one letter that is meaningful instead of 1? eg. M/F (T/B/I) Just an internal thing, but would help a bit the developers or those hitting the db directly

[Eileen]

I just found another thing causing slow queries....

There is no index on civicrm_activity.status_id

Which means I'll have to add it to our sites - but also deal with removing it again on upgrade

[xavier]

Something along that line should work on the upgrade script (untested)

Code: [Select]

set @exist := (select count(*) from information_schema.statistics where table_name = 'civicrm_activity' and index_name = 'status');
set @sqlstmt := if( @exist > 0, 'select ''INFO: Index already exists.''', 'create index status on civicrm_activity ( status_id)');
PREPARE stmt FROM @sqlstmt;
EXECUTE stmt;


[kung]

Could you provide a little more information?
For example, you say "civicrm_option_value.value is a VARCHAR
civicrm_contact.gender_id & the other two are INTEGERS. Seems intuitively like they should be - but ... they shouldn't ... not if they are to join on the option_value.value field"
... but I don't see option_value.value used in the query so I'm not sure how to interpret that.
Also, you mention that "civicrm_contact.gender_id & the other two are INTEGERS" -- could you tell me what the other two are, and are you suggesting they be stored as varchars?
Thanks very much.