CiviCRM Community Forums (archive)

Our CiviCRM install occasionally gets tied up executing queries which are basically a join across many CiviCRM tables followed by a WHERE contact_a.id IN ( [... 36,000 contact IDs ...] )

CiviCRM ends up dropping this to a temporary table, and the queries hang around for a LONG time.

I'd be interested to hear thoughts on where we can attack this so it becomes workable. MySQL tuning? Rewriting CiviCRM's query code (in this case, 36,000 contacts is most of the DB, so perhaps WHERE NOT would be more sensible)?

CiviCRM 2.2.9 / MySQL 5.0.32

Query SQL

EXPLAIN'd:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	contact_a	index	PRIMARY	index_sort_name	387	NULL	39348	Using where; Using temporary
1	SIMPLE	civicrm_address	ref	index_is_primary,FK_civicrm_address_contact_id	FK_civicrm_address_contact_id	5	civicrm.contact_a.id	1
1	SIMPLE	civicrm_county	eq_ref	PRIMARY	PRIMARY	4	civicrm.civicrm_address.county_id	1
1	SIMPLE	civicrm_state_province	eq_ref	PRIMARY	PRIMARY	4	civicrm.civicrm_address.state_province_id	1
1	SIMPLE	civicrm_country	eq_ref	PRIMARY	PRIMARY	4	civicrm.civicrm_address.country_id	1
1	SIMPLE	civicrm_email	ref	index_is_primary,FK_civicrm_email_contact_id	FK_civicrm_email_contact_id	5	civicrm.contact_a.id	1
1	SIMPLE	civicrm_phone	ref	index_is_primary,FK_civicrm_phone_contact_id	FK_civicrm_phone_contact_id	5	civicrm.contact_a.id	1
1	SIMPLE	civicrm_im	ref	index_is_primary,FK_civicrm_im_contact_id	FK_civicrm_im_contact_id	5	civicrm.contact_a.id	1
1	SIMPLE	civicrm_group_contact	ref_or_null	UI_contact_group,group_contact_idx	group_contact_idx	2	const	94838
1	SIMPLE	civicrm_group	eq_ref	PRIMARY	PRIMARY	4	civicrm.civicrm_group_contact.group_id	1
1	SIMPLE	civicrm_entity_tag	ref	UI_contact_id_tag_id,FK_civicrm_entity_tag_contact_id	UI_contact_id_tag_id	4	civicrm.contact_a.id	1	Using index
1	SIMPLE	civicrm_note	ref	index_entity	index_entity	198	const,civicrm.contact_a.id	1
1	SIMPLE	civicrm_worldregion	eq_ref	PRIMARY	PRIMARY	4	civicrm.civicrm_country.region_id	1
1	SIMPLE	civicrm_openid	ref	FK_civicrm_openid_contact_id	FK_civicrm_openid_contact_icivicrm.contact_a.id	1
1	SIMPLE	civicrm_tag	eq_ref	PRIMARY	PRIMARY	4	civicrm.civicrm_entity_tag.tag_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,FK_civicrm_option_value_option_group_id,index_option_group_id_name	index_option_group_id_name	4	civicrm.option_group_gender.id	3
1	SIMPLE	option_group_greeting	const	UI_name	UI_name	194	const	1	Using index
1	SIMPLE	greeting_type	ref	index_option_group_id_value,FK_civicrm_option_value_option_group_id,index_option_group_id_name	FK_civicrm_option_value_option_group_id	4	civicrm.option_group_greeting.id	3
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,FK_civicrm_option_value_option_group_id,index_option_group_id_name	index_option_group_id_name	4	civicrm.option_group_prefix.id	3
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,FK_civicrm_option_value_option_group_id,index_option_group_id_name	FK_civicrm_option_value_option_group_id	4	civicrm.option_group_suffix.id	3

Export all, or possibly a find dupes too. I know that one of our team has been having timeouts with dupe matching, but I don't know if the request which started this particular query off was one of those.

Thanks Lobo. Will look into it ... but have to wait for low-use times to test this in situ, and all the low-use times for the server are high-demand times for Daddy at the moment. Except 4am :S