Indices on civicrm_email could be better

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.