Details
-
Type:
Bug
-
Status:
Open
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 2.2.9
-
Fix Version/s: 4.3
-
Component/s: Core CiviCRM
-
Labels:None
Description
Not sure if this is still the case in current versions, but the indexing of civicrm_email is fairly strange:
1) There's a single field index on is_primary. Not very useful, because there will never be a query for all primary email addresses. This index will be pseudo-combined with other indexes, but this is not ideal.
2) same goes for location_type
3) The index on contact_id is a single field index. Should be multi-field because most queries want to restrict searches on other fields.
3) There is an auto-incrementing PK on this table. Not sure why? Is this table on the 'one' side of any one-to-many relationships?
I'd recommend dropping the indexes for is_primary, contact_id and location_type, and adding two new ones:
contact_id,is_primary
contact_id, location_type, is_primary
These should work for most searches. We could add additional indices on any of the remaining fields if necessary which InnoDB will pseudo-combine when necessary. But I think queries that restrict on the remaining fields are likely only to happen during CiviMail batch operations so I'm not sure the higher cost of inserting/updating records is worth it.
We could then drop the auto-incrementing field and make either of the new indices the PK. We should choose whichever one is used more, probably contact_id, is_primary. As some background as to why you want to do this, InnoDB stores all data in a tree where the trunk is the PK. Thus queries are fastest when using the PK. Most queries to this table have WHERE clauses on either contact_id with is_primary or location_type; or with email. Thus you want to make these queries as fast as possible.
1) There's a single field index on is_primary. Not very useful, because there will never be a query for all primary email addresses. This index will be pseudo-combined with other indexes, but this is not ideal.
2) same goes for location_type
3) The index on contact_id is a single field index. Should be multi-field because most queries want to restrict searches on other fields.
3) There is an auto-incrementing PK on this table. Not sure why? Is this table on the 'one' side of any one-to-many relationships?
I'd recommend dropping the indexes for is_primary, contact_id and location_type, and adding two new ones:
contact_id,is_primary
contact_id, location_type, is_primary
These should work for most searches. We could add additional indices on any of the remaining fields if necessary which InnoDB will pseudo-combine when necessary. But I think queries that restrict on the remaining fields are likely only to happen during CiviMail batch operations so I'm not sure the higher cost of inserting/updating records is worth it.
We could then drop the auto-incrementing field and make either of the new indices the PK. We should choose whichever one is used more, probably contact_id, is_primary. As some background as to why you want to do this, InnoDB stores all data in a tree where the trunk is the PK. Thus queries are fastest when using the PK. Most queries to this table have WHERE clauses on either contact_id with is_primary or location_type; or with email. Thus you want to make these queries as fast as possible.