AnsweredAssumed Answered

Speed issues with List View queries in 7.2 (on site)

Question asked by Steve Cox on Jul 14, 2014
Latest reply on Jan 7, 2015 by Anthony Watson
Hi,

I've installed 7.2.0 Pro on site in a test environment - and set up a large number or accounts (100k+), contacts (200k+). The system is working fine but standard list views are taking a long time: accounts approx 10 seconds, contacts approx 28 seconds.

It's definitely the database query as the timescales are the same pulling a listview via the API.

I've captured the MySQL slow query and running the query directly produces similar times.

The database and web server are separate and configurations are similar to the servers running our live v6 installation with similar numbers of records. I

If we add a filter to the listviews, then the times come down - it appears that the query is pulling all non-deleted records, before sequentially processing each for the related record joins, and then subsequently applying the 1-20 row limit. Ideally, only the base module table should be processed, with the sort order being handled by indexes before the limit is applied and then, joined against the other tables.

I've tried experimenting with adding indexes but with no luck. Any thoughts on what to try?

It's quite difficult to post readable snippets using this site, so I'll attached readable images - but the full query follows if you want to try it locally.



Thanks,
Steve

SELECT DISTINCT contacts.salutation name__salutation, contacts.first_name name__first_name, contacts.last_name name__last_name, 
contacts.title title, jt1_accounts.name account_name, contacts.primary_address_city primary_address_city, contacts.phone_work phone_work,   jt3_email_addresses_primary.email_address email1, jt5_assigned_user_link.first_name assigned_user_name__first_name,   jt5_assigned_user_link.last_name assigned_user_name__last_name, contacts.date_modified date_modified,   contacts_cstm.mm_contacts_contactrole_field_c mm_contacts_contactrole_field_c, contacts_cstm.lead_source_desc_c lead_source_desc_c,   contacts.team_set_id team_set_id, jt6_team_link.name team_name, contacts.department department, contacts.do_not_call do_not_call,   contacts.phone_home phone_home, contacts.phone_mobile phone_mobile, contacts.phone_other phone_other,   contacts.primary_address_country primary_address_country, contacts.primary_address_street primary_address_street,   contacts.primary_address_state primary_address_state, contacts.primary_address_postalcode primary_address_postalcode,   contacts.alt_address_country alt_address_country, contacts.alt_address_street alt_address_street, contacts.alt_address_city alt_address_city,   contacts.alt_address_state alt_address_state, contacts.alt_address_postalcode alt_address_postalcode, contacts.date_entered date_entered,   jt7_created_by_link.first_name created_by_name__first_name, jt7_created_by_link.last_name created_by_name__last_name,   contacts.first_name first_name, contacts.last_name last_name, contacts.salutation salutation, jt1_accounts.id account_id,   contacts.assigned_user_id assigned_user_id, contacts.created_by created_by,   case when jt10_favorite_link.id IS NOT NULL then 1 else 0 end my_favorite,   case when jt8_following_link.id IS NOT NULL then 1 else 0 end following,   contacts.id id, contacts.last_name contacts__last_name, contacts.id contacts__id   FROM contacts   LEFT JOIN sugarfavorites sf_contacts ON (contacts.id = sf_contacts.record_id AND sf_contacts.deleted = 0 AND sf_contacts.module = 'Contacts' AND sf_contacts.created_by = 'c8b5f399-d448-843a-c586-43c7e29c69db')    LEFT JOIN accounts_contacts jt2_accounts_contacts ON (contacts.id = jt2_accounts_contacts.contact_id AND jt2_accounts_contacts.deleted = 0 AND jt2_accounts_contacts.primary_account = '1')    LEFT JOIN accounts jt1_accounts ON (jt1_accounts.id = jt2_accounts_contacts.account_id AND jt1_accounts.deleted = 0)    LEFT JOIN accounts_cstm jt1_accounts_cstm ON (jt1_accounts_cstm.id_c = jt1_accounts.id)    LEFT JOIN email_addr_bean_rel jt4_email_addr_bean_rel ON (contacts.id = jt4_email_addr_bean_rel.bean_id AND jt4_email_addr_bean_rel.deleted = 0 AND jt4_email_addr_bean_rel.primary_address = '1')    LEFT JOIN email_addresses jt3_email_addresses_primary ON (jt3_email_addresses_primary.id = jt4_email_addr_bean_rel.email_address_id AND jt3_email_addresses_primary.deleted = 0)    LEFT JOIN users jt5_assigned_user_link ON (contacts.assigned_user_id = jt5_assigned_user_link.id AND jt5_assigned_user_link.deleted = 0)    LEFT JOIN teams jt6_team_link ON (contacts.team_id = jt6_team_link.id AND jt6_team_link.deleted = 0)    LEFT JOIN users jt7_created_by_link ON (contacts.created_by = jt7_created_by_link.id AND jt7_created_by_link.deleted = 0)    LEFT JOIN subscriptions jt9_subscriptions ON (contacts.id = jt9_subscriptions.parent_id AND jt9_subscriptions.deleted = 0 AND jt9_subscriptions.parent_type = 'Contacts'      AND jt9_subscriptions.created_by = 'c8b5f399-d448-843a-c586-43c7e29c69db')    LEFT JOIN users jt8_following_link ON (jt8_following_link.id = jt9_subscriptions.created_by AND jt8_following_link.deleted = 0)    LEFT JOIN contacts_cstm ON contacts_cstm.id_c = contacts.id    LEFT JOIN users jt10_favorite_link ON (jt10_favorite_link.id = sf_contacts.modified_user_id AND jt10_favorite_link.deleted = 0)      WHERE contacts.deleted = 0  ORDER BY contacts.last_name ASC,contacts.id DESC  LIMIT 0,21;


Outcomes