We try to pull CiviCRM contacts data incl. multiply custom fields and display on map. There are around 400 contacts to display, yet map page loads a bit slower, than expected (5 to 10 seconds). It has been worse initially even at quite powerful server, but we've got significant improvements using combination of:
- Redis
- nginx ngx_http_gzip_module
- fine-tuned MySQL (via mysqltuner.pl)
- Drupal caching and Views Accelerator module
Any advice on how to make it loads as fast as possible (up to, say 2-3 seconds) please?
Drupal 7x and CiviCRM 4.6x
UPD Views query
SELECT civicrm_address.geo_code_1 AS civicrm_address_geo_code_1, civicrm_address.geo_code_2 AS civicrm_address_geo_code_2, civicrm_contact.organization_name AS civicrm_contact_organization_name, civicrm_website.url AS civicrm_website_url, civicrm_value_society_chairman_10.public_contact_for_society_988 AS civicrm_value_society_chairman_10_public_contact_for_society, civicrm_address.street_address AS civicrm_address_street_address, civicrm_contact_civicrm_relationship.id AS civicrm_contact_civicrm_relationship_id, civicrm_value_society_chairman_10.subs_rate_single__38 AS civicrm_value_society_chairman_10_subs_rate_single__38, civicrm_value_society_chairman_10.meeting_day_time_1_531 AS civicrm_value_society_chairman_10_meeting_day_time_1_531, civicrm_value_society_chairman_10.meeting_day_time_2_532 AS civicrm_value_society_chairman_10_meeting_day_time_2_532, civicrm_contact.id AS id FROM {civicrm_contact} civicrm_contact INNER JOIN {civicrm_relationship} civicrm_relationship_civicrm_contact ON civicrm_contact.id = civicrm_relationship_civicrm_contact.contact_id_a AND (civicrm_relationship_civicrm_contact.relationship_type_id = '59' AND civicrm_relationship_civicrm_contact.is_active = '1') LEFT JOIN {civicrm_relationship} civicrm_relationship ON civicrm_contact.id = civicrm_relationship.contact_id_a INNER JOIN {civicrm_contact} civicrm_contact_civicrm_relationship ON civicrm_relationship.contact_id_b = civicrm_contact_civicrm_relationship.id LEFT JOIN {civicrm_address} civicrm_address ON civicrm_contact.id = civicrm_address.contact_id LEFT JOIN {civicrm_website} civicrm_website ON civicrm_contact.id = civicrm_website.contact_id LEFT JOIN {civicrm_value_society_chairman_10} civicrm_value_society_chairman_10 ON civicrm_contact.id = civicrm_value_society_chairman_10.entity_id WHERE (( (civicrm_address.geo_code_1 IS NOT NULL ) AND (civicrm_address.geo_code_2 IS NOT NULL ) AND (civicrm_contact.contact_type IN ('Organization')) AND (civicrm_contact.contact_sub_type IN ('Society')) )) LIMIT 1000 OFFSET 0