Not the answer you need?
Register and ask your own question!

Avoid filesort when using ORDER BY LIMIT

kumargsvnkumargsvn ContributorCurrent User Role Participant
SQL :
SELECT
CASE
WHEN jt1_following_link.id IS NOT NULL THEN 1
ELSE 0
END following,
sf_contacts.id my_favorite,
contacts.salutation,
contacts.first_name,
contacts.last_name,
contacts.title,
jt2_accounts.id account_id,
jt2_accounts.assigned_user_id account_id_owner,
jt2_accounts.name account_name,
jt2_accounts.assigned_user_id account_name_owner,
jt3_email_addresses_primary.email_address email,
contacts.phone_work,
contacts.date_modified,
contacts.date_entered,
contacts.id,
contacts.assigned_user_id,
contacts.created_by,
contacts_erased.data erased_fields
FROM
contacts
LEFT JOIN
erased_fields contacts_erased ON contacts_erased.bean_id = contacts.id
AND contacts_erased.table_name = 'contacts'
LEFT JOIN
sugarfavorites sf_contacts ON (sf_contacts.module = 'Contacts')
AND (sf_contacts.record_id = contacts.id)
AND (sf_contacts.assigned_user_id = '1')
AND (sf_contacts.deleted = '0')
LEFT JOIN
subscriptions contacts_following ON (contacts.id = contacts_following.parent_id)
AND (contacts_following.deleted = '0')
AND (contacts_following.parent_type = 'Contacts')
AND (contacts_following.created_by = '1')
LEFT JOIN
users jt1_following_link ON (jt1_following_link.id = contacts_following.created_by)
AND (jt1_following_link.deleted = '0')
LEFT JOIN
accounts_contacts accounts_contacts ON (contacts.id = accounts_contacts.contact_id)
AND (accounts_contacts.deleted = '0')
AND (accounts_contacts.primary_account = '1')
LEFT JOIN
accounts jt2_accounts ON (jt2_accounts.id = accounts_contacts.account_id)
AND (jt2_accounts.deleted = '0')
LEFT JOIN
email_addr_bean_rel contacts_email_addresses_primary ON (contacts.id = contacts_email_addresses_primary.bean_id)
AND (contacts_email_addresses_primary.deleted = '0')
AND (contacts_email_addresses_primary.bean_module = 'Contacts')
AND (contacts_email_addresses_primary.primary_address = '1')
LEFT JOIN
email_addresses jt3_email_addresses_primary ON (jt3_email_addresses_primary.id = contacts_email_addresses_primary.email_address_id)
AND (jt3_email_addresses_primary.deleted = '0')
WHERE
contacts.deleted = '0'
ORDER BY contacts.date_modified DESC, contacts.id DESC
LIMIT 21

Tables structure:
CREATE TABLE `contacts` (
`id` char(36) NOT NULL,
`date_entered` datetime DEFAULT NULL,
`date_modified` datetime DEFAULT NULL,
`modified_user_id` char(36) DEFAULT NULL,
`created_by` char(36) DEFAULT NULL,
`description` mediumtext,
`deleted` tinyint(1) DEFAULT '0',
`salutation` varchar(255) DEFAULT NULL,
`first_name` varchar(100) DEFAULT NULL,
`last_name` varchar(100) DEFAULT NULL,
`title` varchar(150) DEFAULT NULL,
`facebook` varchar(100) DEFAULT NULL,
`twitter` varchar(100) DEFAULT NULL,
`googleplus` varchar(100) DEFAULT NULL,
`department` varchar(255) DEFAULT NULL,
`do_not_call` tinyint(1) DEFAULT '0',
`phone_home` varchar(100) DEFAULT NULL,
`phone_mobile` varchar(100) DEFAULT NULL,
`phone_work` varchar(100) DEFAULT NULL,
`phone_other` varchar(100) DEFAULT NULL,
`phone_fax` varchar(100) DEFAULT NULL,
`primary_address_street` varchar(150) DEFAULT NULL,
`primary_address_city` varchar(100) DEFAULT NULL,
`primary_address_state` varchar(100) DEFAULT NULL,
`primary_address_postalcode` varchar(20) DEFAULT NULL,
`primary_address_country` varchar(255) DEFAULT NULL,
`alt_address_street` varchar(150) DEFAULT NULL,
`alt_address_city` varchar(100) DEFAULT NULL,
`alt_address_state` varchar(100) DEFAULT NULL,
`alt_address_postalcode` varchar(20) DEFAULT NULL,
`alt_address_country` varchar(255) DEFAULT NULL,
`assistant` varchar(75) DEFAULT NULL,
`assistant_phone` varchar(100) DEFAULT NULL,
`picture` varchar(255) DEFAULT NULL,
`lead_source` varchar(255) DEFAULT NULL,
`dnb_principal_id` varchar(30) DEFAULT NULL,
`reports_to_id` char(36) DEFAULT NULL,
`birthdate` date DEFAULT NULL,
`portal_name` varchar(255) DEFAULT NULL,
`portal_active` tinyint(1) DEFAULT '0',
`portal_password` varchar(255) DEFAULT NULL,
`portal_app` varchar(255) DEFAULT NULL,
`preferred_language` varchar(255) DEFAULT NULL,
`campaign_id` char(36) DEFAULT NULL,
`mkto_sync` tinyint(1) DEFAULT '0',
`mkto_id` int(11) DEFAULT NULL,
`mkto_lead_score` int(11) DEFAULT NULL,
`assigned_user_id` char(36) DEFAULT NULL,
`team_id` char(36) DEFAULT NULL,
`team_set_id` char(36) DEFAULT NULL,
`acl_team_set_id` char(36) DEFAULT NULL,
`g_assistant_email` varchar(255) DEFAULT NULL COMMENT 'Assistant Email Address.',
`g_client_type` varchar(255) DEFAULT NULL COMMENT 'Client Type, Member,Prospect etc.',
`g_gold_contact_id` int(50) DEFAULT NULL COMMENT 'Non-unique MDM ID.',
`g_linkedin` varchar(255) DEFAULT NULL COMMENT 'Linkedin details.',
`g_phone_mobile_formatted` varchar(100) DEFAULT NULL COMMENT 'Formatted mobile number.',
`g_phone_work_formatted` varchar(100) DEFAULT NULL COMMENT 'Formatted work number.',
`g_ppl_code` int(11) DEFAULT NULL COMMENT 'Athena ppl_code.',
`g_external_id` varchar(50) DEFAULT NULL COMMENT 'External source system id.',
`g_external_source` varchar(10) DEFAULT NULL COMMENT 'External source system Name.',
`g_rb_premium_service_id` char(36) DEFAULT NULL,
`dp_business_purpose` mediumtext,
`dp_consent_last_updated` date DEFAULT NULL,
`g_assistant_phone_formatted` varchar(100) DEFAULT NULL COMMENT 'Formatted phone number from assistant_phone field.',
`g_job_function` varchar(255) DEFAULT NULL COMMENT 'Job Function details',
`g_job_role` varchar(255) DEFAULT NULL COMMENT 'Job Role details',
`g_whatsapp` varchar(50) DEFAULT NULL COMMENT 'WhatsApp Number',
`g_seatholder` tinyint(1) DEFAULT '0' COMMENT 'A calculated flag for contacts who are associated with and active purchased product.',
`g_level_classification` varchar(50) DEFAULT NULL COMMENT 'Classification Level',
`g_suffix_name` varchar(20) DEFAULT NULL COMMENT 'Contact Suffix Name',
`g_is_receivables_contact` tinyint(1) DEFAULT '0' COMMENT 'Is receivables contact flag',
`g_role` varchar(255) DEFAULT NULL,
`g_function` varchar(255) DEFAULT NULL,
`g_tier` varchar(50) DEFAULT NULL COMMENT 'Field to segment the Prospects for cross sell and outreach',
`g_campaign_tag` varchar(500) DEFAULT NULL COMMENT 'Generic field to identify prospects tagged with a Campaign/Program for targeted outreach ',
PRIMARY KEY (`id`),
UNIQUE KEY `unq_ppl_cd` (`g_ppl_code`),
UNIQUE KEY `unq_ppl_code` (`g_ppl_code`),
KEY `idx_contacts_date_modfied` (`date_modified`),
KEY `idx_contacts_id_del` (`id`,`deleted`),
KEY `idx_contacts_date_entered` (`date_entered`),
KEY `idx_contacts_last_first` (`last_name`,`first_name`,`deleted`),
KEY `idx_contacts_first_last` (`first_name`,`last_name`,`deleted`),
KEY `idx_contacts_del_last` (`deleted`,`last_name`),
KEY `idx_cont_del_reports` (`deleted`,`reports_to_id`,`last_name`),
KEY `idx_reports_to_id` (`reports_to_id`),
KEY `idx_del_id_user` (`deleted`,`id`,`assigned_user_id`),
KEY `idx_cont_assigned` (`assigned_user_id`),
KEY `idx_contact_title` (`title`),
KEY `idx_contact_mkto_id` (`mkto_id`),
KEY `idx_contacts_assigned_del` (`assigned_user_id`,`deleted`),
KEY `idx_contact_external_id` (`g_external_id`),
KEY `idx_contacts_external_id` (`g_external_id`),
KEY `idx_g_extended_contact_id` (`g_rb_premium_service_id`),
KEY `idx_contacts_g_external_id` (`g_external_id`),
KEY `idx_contacts_tmst_id` (`team_set_id`,`deleted`),
KEY `idx_contacts_acl_tmst_id` (`acl_team_set_id`,`deleted`),
KEY `idx_contacts_date_modified_id` (`date_modified`,`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4



CREATE TABLE `erased_fields` (
`bean_id` char(36) NOT NULL,
`table_name` varchar(128) NOT NULL,
`data` mediumtext,
PRIMARY KEY (`bean_id`,`table_name`),
KEY `idx_erased_fields_bean_id` (`bean_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.