(An Untitled Masterwork)
ActivePublic
Actions

Authored by jcrespo on Aug 29 2017, 1:15 PM.

Tags

None

Referenced Files

	F9220123:
	Aug 30 2017, 9:36 AM

	F9204340:
	Aug 29 2017, 1:15 PM

Subscribers

Ejegg

	Before:

	CREATE TABLE civicrm_group_contact_cache (
	id int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
	group_id int(10) unsigned NOT NULL COMMENT 'FK to civicrm_group',
	contact_id int(10) unsigned NOT NULL COMMENT 'FK to civicrm_contact',
	PRIMARY KEY (id),
	UNIQUE KEY UI_contact_group (contact_id,group_id),
	KEY FK_civicrm_group_contact_cache_group_id (group_id),
	CONSTRAINT FK_civicrm_group_contact_cache_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact (id) ON DELETE CASCADE,
	CONSTRAINT FK_civicrm_group_contact_cache_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group (id) ON DELETE CASCADE
	) ENGINE=InnoDB

	After:

	CREATE TABLE civicrm_group_contact_cache (
	group_id int(10) unsigned NOT NULL COMMENT 'FK to civicrm_group',
	contact_id int(10) unsigned NOT NULL COMMENT 'FK to civicrm_contact',
	PRIMARY KEY (group_id, contact_id),
	/* UNIQUE KEY UI_contact_group (contact_id,group_id), */
	KEY FK_civicrm_group_contact_cache_group_id (group_id),
	CONSTRAINT FK_civicrm_group_contact_cache_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact (id) ON DELETE CASCADE,
	CONSTRAINT FK_civicrm_group_contact_cache_group_id FOREIGN KEY (group_id) REFERENCES civicrm_group (id) ON DELETE CASCADE
	) ENGINE=InnoDB

Event Timeline

jcrespo created this paste.Aug 29 2017, 1:15 PM

Thanks @jcrespo!
Is the UNIQUE_KEY redundant now, or does the different column order mean something?

Ejegg mentioned this in T174404: Drop IDs on cache tables.Aug 29 2017, 5:33 PM

@Ejegg I would definitely drop it or at least drop its uniquness. If you only do "group_id = X AND contact_id = Y" or "group_id = X" or "contact_id = Y" conditions, it is unnecessary, (PRIMARY or UI_contact_group can be used instead). There are some cases in which you may want to keep it, which is if you do something like ORDER BY contact_id,group_id mostly. Delete it otherwise.

Beware of the performance penalty too, of page splits due to inserting out-of-order.

jcrespo edited the content of this paste. (Show Details)Aug 30 2017, 9:36 AM

(An Untitled Masterwork)ActivePublicActions

Event Timeline

(An Untitled Masterwork)
ActivePublic
Actions