StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCustomized Customer Grid causing MySQL table scans and filesorts aka slow performance
text
Body
copied!<p>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).</p> <p>Here is the code snippet were the collection is being generated in the Customer <code>Grid.php</code> <code>Block</code> file. Nothing fancy or out of ordinary, simply adding attributes to the collection mainly.</p> <pre><code>$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); </code></pre> <p>Generates this query, which is misbehaving causing very long load times while in the customer grid:</p> <pre><code>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 </code></pre> <p>an <code>EXPLAIN</code> on the query shows, NOTE the Extra on table e, <em>Using temporary and Using filesort</em>:</p> <pre><code>*************************** 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) </code></pre> <p>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: <a href="http://www.magereverse.com/index/magento-sql-structure/version/1-5-1-0" rel="noreferrer">http://www.magereverse.com/index/magento-sql-structure/version/1-5-1-0</a> </p> <p>Here is the alias table referred to AS e. on the scan:</p> <pre><code>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 ) </code></pre> <p><strong>So the question is how can I get this query to perform better and not cause the temporary tables being created and scanned.</strong></p> <p>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. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload