Tweet
Hi, I am in the process of importing a lot of Contacts into the system (50,000+). I have already loaded up the Accounts which went well, but the Contact data is slow and getting slower (about 1 row per second as I write with 12,000 rows loaded and lots more to come)
So I dug out the MySQL tools and went to work to identify what was going on.
I set up the trap for slow running queries and one specific query came to the top of the list. It alone takes about 0.6 seconds of the 0.7 second per row.
I then spent some time with the EXPLAIN tool and worked out that a complete table scan of the Contact table is being performed every time the query runs.
The full query is below but this is the problematic segment:
The actual problem is caused by the two items in the OR clause. If I take either clause out, or change the "OR" to an "AND" then the index is used and the query is almost instant. As is it avoids the table scan.
I don't know why the query optimiser is doing this or what can be done about it but imports are very slow.
Help would be appreciated.
In my specific situation I know that the records are new so I am happy to skip the check to see if they are there already.
For reference here is the full query:
So I dug out the MySQL tools and went to work to identify what was going on.
I set up the trap for slow running queries and one specific query came to the top of the list. It alone takes about 0.6 seconds of the 0.7 second per row.
I then spent some time with the EXPLAIN tool and worked out that a complete table scan of the Contact table is being performed every time the query runs.
The full query is below but this is the problematic segment:
Code:
WHERE ((contact.first_name = 'Joe' AND contact.last_name = 'Bloggs') OR (emailAddressesMultiple.name = 'joe.bloggs@foo.com')) AND contact.deleted = '0'
I don't know why the query optimiser is doing this or what can be done about it but imports are very slow.
Help would be appreciated.
In my specific situation I know that the records are new so I am happy to skip the check to see if they are there already.
For reference here is the full query:
Code:
SELECT DISTINCT contact.id AS `id` , TRIM(CONCAT(IFNULL(contact.first_name,''),' ', IFNULL(contact.last_name,''))) AS `name`, contact.deleted AS `deleted`, contact.salutation_name AS `salutationName`, contact.first_name AS `firstName`, contact.last_name AS `lastName`, contact.account_id AS `accountId`, accountContact.role AS `title`, contact.description AS `description`, emailAddresses.name AS `emailAddress`, phoneNumbers.name AS `phoneNumber`, contact.do_not_call AS `doNotCall`, contact.address_street AS `addressStreet`, contact.address_city AS `addressCity`, contact.address_state AS `addressState`, contact.address_country AS `addressCountry`, contact.address_postal_code AS `addressPostalCode`, account.type AS `accountType`, contact.created_at AS `createdAt`, contact.modified_at AS `modifiedAt`, contact.core_id AS `coreId`, contact.core_id_num AS `coreIdNum`, account.name AS `accountName`, contact.campaign_id AS `campaignId`, contact.created_by_id AS `createdById`, TRIM(CONCAT(createdBy.first_name,' ', createdBy.last_name)) AS `createdByName`, contact.modified_by_id AS `modifiedById`, TRIM(CONCAT(modifiedBy.first_name,' ', modifiedBy.last_name)) AS `modifiedByName`, contact.assigned_user_id AS `assignedUserId`, TRIM(CONCAT(assignedUser.first_name,' ', assignedUser.last_name)) AS `assignedUserName` FROM `contact` LEFT JOIN `account` AS `account` ON contact.account_id = account.id LEFT JOIN `user` AS `assignedUser` ON contact.assigned_user_id = assignedUser.id LEFT JOIN `user` AS `modifiedBy` ON contact.modified_by_id = modifiedBy.id LEFT JOIN `user` AS `createdBy` ON contact.created_by_id = createdBy.id LEFT JOIN `entity_email_address` AS `emailAddressesMiddle` ON contact.id = emailAddressesMiddle.entity_id AND emailAddressesMiddle.deleted = '0' AND emailAddressesMiddle.primary = '1' AND emailAddressesMiddle.entity_type = 'Contact' LEFT JOIN `email_address` AS `emailAddresses` ON emailAddresses.id = emailAddressesMiddle.email_address_id AND emailAddresses.deleted ='0' LEFT JOIN `entity_phone_number` AS `phoneNumbersMiddle` ON contact.id = phoneNumbersMiddle.entity_id AND phoneNumbersMiddle.deleted = '0' AND phoneNumbersMiddle.primary = '1' AND phoneNumbersMiddle.entity_type = 'Contact' LEFT JOIN `phone_number` AS `phoneNumbers` ON phoneNumbers.id = phoneNumbersMiddle.phone_number_id AND phoneNumbers.deleted = '0' LEFT JOIN `entity_email_address` AS `emailAddressesMultipleMiddle` ON contact.id = emailAddressesMultipleMiddle.entity_id AND emailAddressesMultipleMiddle.deleted = '0' AND emailAddressesMultipleMiddle.entity_type = 'Contact' LEFT JOIN `email_address` AS `emailAddressesMultiple` ON emailAddressesMultiple.id = emailAddressesMultipleMiddle.email_address_id AND emailAddressesMultiple.deleted = '0' LEFT JOIN `account_contact` AS accountContact ON accountContact.contact_id = contact.id AND accountContact.account_id = contact.account_id AND accountContact.deleted = 0 WHERE ((contact.first_name = 'John' AND contact.last_name = 'Doe') OR (emailAddressesMultiple.name = 'john.doe@foo.com')) AND contact.deleted = '0'
Comment