EDIT: I had this question originally posted on Stackoverflow that never got fully answered, I'm posting in here in hopes to shed anymore light on why the massive table scans and any means to prevent such with (hopefully) minimal core changes.
Magento Enterprise. 1.10.1.1. The data set of customers and addresses are semi-large (125k+) CSRs are often on this grid (sometimes 25+ concurrent users at a time).
Here is the code snippet were the collection is being generated in the Customer Grid.php
Block
file. Nothing fancy or out of ordinary, simply adding attributes to the collection mainly.
$collection = Mage::getResourceModel('customer/customer_collection')
->addNameToSelect()
->addAttributeToSelect('email')
->addAttributeToSelect('group_id')
->addAttributeToSelect('prod_codes')
->addAttributeToSelect('last_called_date')
->addAttributeToSelect('time_zone')
->addAttributeToSelect('salesrep')
->addAttributeToSelect('do_not_call')
->addAttributeToSelect('club_member')
->addAttributeToSelect('call_back_date')
->addAttributeToSelect('marketing_code_outcome')
->joinAttribute('billing_postcode', 'customer_address/postcode', 'default_billing', null, 'left')
->joinAttribute('billing_city', 'customer_address/city', 'default_billing', null, 'left')
->joinAttribute('billing_telephone', 'customer_address/telephone', 'default_billing', null, 'left')
->joinAttribute('billing_region', 'customer_address/region', 'default_billing', null, 'left');
$this->setCollection($collection);
Generates this query, which is misbehaving causing very long load times while in the customer grid:
SELECT
e . *,
_table_prefix.value AS prefix,
_table_firstname.value AS firstname,
_table_middlename.value AS middlename,
_table_lastname.value AS lastname,
_table_suffix.value AS suffix,
CONCAT(IF(_table_prefix.value IS NOT NULL AND _table_prefix.value != '',
CONCAT(TRIM(_table_prefix.value), ' '),
''),
TRIM(_table_firstname.value),
IF(_table_middlename.value IS NOT NULL AND _table_middlename.value != '',
CONCAT(' ', TRIM(_table_middlename.value)),
''),
' ',
TRIM(_table_lastname.value),
IF(_table_suffix.value IS NOT NULL AND _table_suffix.value != '',
CONCAT(' ', TRIM(_table_suffix.value)),
'')) AS name,
_table_default_billing.value AS default_billing,
_table_billing_postcode.value AS billing_postcode,
_table_billing_city.value AS billing_city,
_table_billing_telephone.value AS billing_telephone,
_table_billing_region.value AS billing_region
FROM
customer_entity AS e
LEFT JOIN
customer_entity_varchar AS _table_prefix ON (_table_prefix.entity_id = e.entity_id) AND (_table_prefix.attribute_id = '4')
LEFT JOIN
customer_entity_varchar AS _table_firstname ON (_table_firstname.entity_id = e.entity_id) AND (_table_firstname.attribute_id = '5')
LEFT JOIN
customer_entity_varchar AS _table_middlename ON (_table_middlename.entity_id = e.entity_id) AND (_table_middlename.attribute_id = '6')
LEFT JOIN
customer_entity_varchar AS _table_lastname ON (_table_lastname.entity_id = e.entity_id) AND (_table_lastname.attribute_id = '7')
LEFT JOIN
customer_entity_varchar AS _table_suffix ON (_table_suffix.entity_id = e.entity_id) AND (_table_suffix.attribute_id = '8')
LEFT JOIN
customer_entity_int AS _table_default_billing ON (_table_default_billing.entity_id = e.entity_id) AND (_table_default_billing.attribute_id = '13')
LEFT JOIN
customer_address_entity_varchar AS _table_billing_postcode ON (_table_billing_postcode.entity_id = _table_default_billing.value) AND (_table_billing_postcode.attribute_id = '29')
LEFT JOIN
customer_address_entity_varchar AS _table_billing_city ON (_table_billing_city.entity_id = _table_default_billing.value) AND (_table_billing_city.attribute_id = '25')
LEFT JOIN
customer_address_entity_varchar AS _table_billing_telephone ON (_table_billing_telephone.entity_id = _table_default_billing.value) AND (_table_billing_telephone.attribute_id = '30')
LEFT JOIN
customer_address_entity_varchar AS _table_billing_region ON (_table_billing_region.entity_id = _table_default_billing.value) AND (_table_billing_region.attribute_id = '27')
WHERE
(e.entity_type_id = '1')
ORDER BY CONCAT(IF(_table_prefix.value IS NOT NULL AND _table_prefix.value != '',
CONCAT(TRIM(_table_prefix.value), ' '),
''),
TRIM(_table_firstname.value),
IF(_table_middlename.value IS NOT NULL AND _table_middlename.value != '',
CONCAT(' ', TRIM(_table_middlename.value)),
''),
' ',
TRIM(_table_lastname.value),
IF(_table_suffix.value IS NOT NULL AND _table_suffix.value != '',
CONCAT(' ', TRIM(_table_suffix.value)),
'')) desc
LIMIT 20 OFFSET 60
an EXPLAIN
on the query shows, NOTE the Extra on table e, Using temporary and Using filesort:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: e
type: ref
possible_keys: IDX_ENTITY_TYPE
key: IDX_ENTITY_TYPE
key_len: 2
ref: const
rows: 55556
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: _table_prefix
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_VARCHAR_ATTRIBUTE,FK_CUSTOMER_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod.e.entity_id,const
rows: 1
Extra:
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: _table_firstname
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_VARCHAR_ATTRIBUTE,FK_CUSTOMER_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod.e.entity_id,const
rows: 1
Extra:
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: _table_middlename
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_VARCHAR_ATTRIBUTE,FK_CUSTOMER_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod.e.entity_id,const
rows: 1
Extra:
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: _table_lastname
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_VARCHAR_ATTRIBUTE,FK_CUSTOMER_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod.e.entity_id,const
rows: 1
Extra:
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: _table_suffix
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_VARCHAR_ATTRIBUTE,FK_CUSTOMER_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod.e.entity_id,const
rows: 1
Extra:
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: _table_default_billing
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_INT_ATTRIBUTE,FK_CUSTOMER_INT_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod.e.entity_id,const
rows: 1
Extra:
*************************** 8. row ***************************
id: 1
select_type: SIMPLE
table: _table_billing_postcode
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_ADDRESS_VARCHAR_ATTRIBUTE,FK_CUSTOMER_ADDRESS_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod._table_default_billing.value,const
rows: 1
Extra:
*************************** 9. row ***************************
id: 1
select_type: SIMPLE
table: _table_billing_city
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_ADDRESS_VARCHAR_ATTRIBUTE,FK_CUSTOMER_ADDRESS_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod._table_default_billing.value,const
rows: 1
Extra:
*************************** 10. row ***************************
id: 1
select_type: SIMPLE
table: _table_billing_telephone
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_ADDRESS_VARCHAR_ATTRIBUTE,FK_CUSTOMER_ADDRESS_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod._table_default_billing.value,const
rows: 1
Extra:
*************************** 11. row ***************************
id: 1
select_type: SIMPLE
table: _table_billing_region
type: eq_ref
possible_keys: IDX_ATTRIBUTE_VALUE,FK_CUSTOMER_ADDRESS_VARCHAR_ATTRIBUTE,FK_CUSTOMER_ADDRESS_VARCHAR_ENTITY,IDX_VALUE
key: IDX_ATTRIBUTE_VALUE
key_len: 6
ref: prod._table_default_billing.value,const
rows: 1
Extra:
11 rows in set (0.00 sec)
No indexes have been modified other than the default ones from Magento themselves for 1.10.1 See Structure of 1.5.1 (CE) here: http://www.magereverse.com/index/magento-sql-structure/version/1-5-1-0
Here is the alias table referred to AS e. on the scan:
CREATE TABLE `customer_entity` (
`entity_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`entity_type_id` SMALLINT(8) UNSIGNED NOT NULL DEFAULT '0',
`attribute_set_id` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '0',
`website_id` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
`email` VARCHAR(255) NOT NULL DEFAULT '',
`group_id` SMALLINT(3) UNSIGNED NOT NULL DEFAULT '0',
`increment_id` VARCHAR(50) NOT NULL DEFAULT '',
`store_id` SMALLINT(5) UNSIGNED NULL DEFAULT '0',
`created_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
`is_active` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`entity_id`),
INDEX `FK_CUSTOMER_ENTITY_STORE` (`store_id`),
INDEX `IDX_ENTITY_TYPE` (`entity_type_id`),
INDEX `IDX_AUTH` (`email`, `website_id`),
INDEX `FK_CUSTOMER_WEBSITE` (`website_id`),
CONSTRAINT `FK_CUSTOMER_ENTITY_STORE` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON UPDATE CASCADE ON DELETE SET NULL,
CONSTRAINT `FK_CUSTOMER_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON UPDATE CASCADE ON DELETE SET NULL
)
So the question is how can I get this query to perform better and not cause the temporary tables being created and scanned.
I'm not exactly sure what I can index to improve this queries performance, and I don't want to dig too much into modifying Magento's ORM.