I'm having trouble with extremely slow exports when the 'groups' field is included, in 4.6.17. I tracked it down to the main export query, and taking out the irrelevant stuff it breaks down to:
SELECT * FROM civicrm_contact contact_a LEFT JOIN civicrm_group_contact ON civicrm_group_contact.contact_id = contact_a.id LEFT JOIN civicrm_group_contact_cache ON civicrm_group_contact_cache.contact_id = contact_a.id LEFT JOIN civicrm_group ON ( civicrm_group.id = civicrm_group_contact.group_id OR civicrm_group.id = civicrm_group_contact_cache.group_id )
The problem is the 'OR' in the final JOIN. With the 'OR' it takes 400s, but reduce it to either of the two conditions and it's 10s. This is with 140k contacts.
I've hacked a fix by manually removing 'groups' from CRM/Export/BAO/Export.php. Because I don't actually need to export groups, and they're included by default in the 'primary' fields.
But I'm confused about why this isn't happening to everyone (I figure people would be shouting about this one, but I can't see anything). I'm worried I'm missing an index somewhere, or my db is otherwise broken.
Could someone else with a large db please try the above query, and let me know if it takes a long time?
In our case civicrm_group_contact_cache is empty, and civicrm_group_contact has 232k rows.