Performance: Indexes on varchar fields should be limited in length

CiviCRM generally has too many indexes (which slows down inserts) and the existing indexes are not very well defined (which slows down searches). This stems from research I've been doing in CRM-5556. One area for improvement is indexes on varchar fields.

Take for example the email field. emails could be anywhere from ~10 characters to 64 characters and beyond (I'm not quite sure what the spec says the absolute limit is). So if you have one email in the table that is 64 characters then that means the index is ~ 192 bytes wide (UTF characters X 3 = # of bytes (approx)). This means that searching by email address is going to be a lot slower than it could be. The better way to go is to have your indexes have a cardinality approximately 95% of the number of rows in the table.

SELECT COUNT FROM civicrm_email WHERE LENGTH(email) > 26

gives you approx. 5% of the rows in the table. So I think we should limit the length of the index on the email field to 26 characters (26 characters ~ 78 bytes).

ALTER TABLE civicrm_email
DROP INDEX UI_email,
ADD INDEX UI_email (email (26))ADD INDEX UI_email (email (26))

This applies to any indexes on varchar fields. Most importantly email, first_name, last_name, organization_name, household_name, street_name, city, sort_name.

For more info see:
http://www.mysql.com/news-and-events/newsletter/2002-10/a0000000075.html
(This one applies to InnoDB, the other recommendations in here are MyISAM only).